Início / computer / Perguntas / 1661181
Accepted
Darren Bartrup-Cook
Asked: 2021-07-08 03:11:08 +0800 CST

Renomeie ou expanda um número variável de colunas com PowerQuery

  • 772

Eu tenho uma tabela de dados que se parece com abaixo:

Report Date |   ID      |   Percent
11/06/2021  |   12345   |   0.9
18/06/2021  |   12345   |   1
25/06/2021  |   12345   |   0.85
02/07/2021  |   12345   |   0.5
11/06/2021  |   54321   |   0.77
18/06/2021  |   54321   |   1
25/06/2021  |   54321   |   1
02/07/2021  |   54321   |   0.25

Cada ID aparece para cada data junto com um valor de porcentagem.
Às vezes, há quatro semanas na tabela, às vezes cinco, dependendo do período de pagamento (um mês pode ter quatro ou cinco semanas).

Eu tenho uma consulta que agrupa os dados por ID e retorna um valor percentual médio nas quatro semanas.

Agora, espero que sem ter que entrar em detalhes completos, quero mudar essas datas para Semana 1, Semana 2, Semana 3, Semana 4 e ocasionalmente Semana 5 (meu chefe quer ver as semanas individuais ao lado do valor médio).
Se eu dinamizar a tabela para que cada semana se torne uma coluna, preciso saber o nome da coluna antes de expandi-la:

let
    Source = Table.NestedJoin(#"My Query", {"ID"}, #"Pivotted Table", {"ID"}, "Pivotted Table", JoinKind.LeftOuter),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "Pivotted Table", "Percent"}),
    #"Expanded Pivotted Table" = Table.ExpandTableColumn(#"Reordered Columns", "Pivotted Table", {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"}, {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"})
in
    #"Expanded Pivotted Table"

Encontrei uma função que me permite renomear colunas com base em sua posição na tabela para que eu possa padronizar os nomes das colunas para Semana 1, Semana 2, Semana 3 etc. Isso ainda gera um erro, pois há uma Semana 5 ocasional.

//Function Name - Rename Columns
let
    RenameColumns = (InputTable as table, ColumnNumbers as list, NewColumnNames as list) =>
let
    OldColumnNames = Table.ColumnNames(InputTable),
    Indexed = List.Zip({OldColumnNames, {0..-1+List.Count(OldColumnNames)}}),
    Filtered = List.Select(Indexed, each List.Contains(ColumnNumbers,_{1})),
    IndexRemoved = List.Transform(Filtered, each _{0}),
    RenameList = List.Zip({IndexRemoved,NewColumnNames}),
    RenamedColumns = Table.RenameColumns(InputTable, RenameList)
in
    RenamedColumns
in
    RenameColumns

Portanto, isso gera um erro se não houver sexta coluna (a primeira coluna é a coluna 0).

//Query Name - Pivotted Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"ID", type text}, {"Percent", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB")[#"Report Date"]), "Report Date", "Percent"),
    RenameColumns = #"Rename Columns"(#"Pivoted Column",{1,2,3,4,5}, {"Week 1", "Week 2", "Week 3", "Week 4", "Week 5"})
in
     RenameColumns

Pergunta:

Como renomear ou expandir um número variável de colunas?

microsoft-excel power-query

1 respostas

  1. Best Answer

    Começando com sua data bruta (não a tabela dinâmica), o seguinte deve permitir um número variável de colunas.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Report Date", type date}, {"ID", Int64.Type}, {"Percent", type number}}),

//Create the arrays for the week date => weekname transformation
//Assuming that weeks are numbered according to the position in a date-sorted list, and not by the week number of the month or year
//Column Names must be strings, so we have to convert the dates to the equivalent strings
oldNames = List.Transform(List.Sort(List.Distinct(#"Changed Type"[#"Report Date"])), each Text.From(_)),

//use List.Generate to create the new column names
newNames = List.Generate(()=>[colName="Week 1", IDX=2],
                each [IDX] < List.Count(oldNames)+2,
                each [colName="Week" & Text.From([IDX]), IDX = [IDX]+1],
                each [colName]),

//Then group and Pivot however you have done this
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Report Date", "ID"}, {{"Average", each List.Average([Percent]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", 
        {{"Report Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Report Date", type text}}, "en-US")[#"Report Date"]), "Report Date", "Average", List.Average),

//Rename the columns here
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",List.Zip({oldNames,newNames})),

//Re-Order the columns to ensure they are in the proper sorted order
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",newNames)
in
    #"Reordered Columns"
    • 1

relate perguntas