Forum Discussion

Hamster0406's avatar
Hamster0406
Frequent Visitor
3 years ago
Solved

PQ transformation with label and values in different columns

I have a dataset where profit centre of expenses and values for those get exported in different column as below. We need all the profit centres and their values in only 2 columns. So Amount_1_title g...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hi,

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Data = Table.AddColumn(Source, "Data", each 
        List.Select(
          List.Zip({
            List.Range(Record.ToList(_),2,7),
            List.Range(Record.ToList(_),9,7)}),
          each _{1}<>0)),
    Select_Columns = Table.SelectColumns(Data,{"AccountName", "AccountFullName", "Data"}),
    ExpandList = Table.ExpandListColumn(Select_Columns, "Data"),
    Record = Table.TransformColumns(ExpandList, {{"Data", each Record.FromList(_,{"Class","Amount"})}}),
    ExpandRecord = Table.ExpandRecordColumn(Record,"Data",{"Class","Amount"})
    in
    ExpandRecord

     

     

     let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TransformMany = List.TransformMany(Table.ToRows(Source), each {1..7}, (x,y)=>{x{0},x{1},x{y+1},x{y+8}}),
    TableFromRows = Table.FromRows(TransformMany,{"AccountName","AccountFullName","Class","Amount"}),
    #"Filter<>0" = Table.SelectRows(TableFromRows, each [Amount] <> 0)
    in
    #"Filter<>0"

     

    Stéphane

slorin's avatar
slorin
Icon for Super User rankSuper User
3 years ago

Hi,

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Data = Table.AddColumn(Source, "Data", each 
    List.Select(
      List.Zip({
        List.Range(Record.ToList(_),2,7),
        List.Range(Record.ToList(_),9,7)}),
      each _{1}<>0)),
Select_Columns = Table.SelectColumns(Data,{"AccountName", "AccountFullName", "Data"}),
ExpandList = Table.ExpandListColumn(Select_Columns, "Data"),
Record = Table.TransformColumns(ExpandList, {{"Data", each Record.FromList(_,{"Class","Amount"})}}),
ExpandRecord = Table.ExpandRecordColumn(Record,"Data",{"Class","Amount"})
in
ExpandRecord

 

 

 let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TransformMany = List.TransformMany(Table.ToRows(Source), each {1..7}, (x,y)=>{x{0},x{1},x{y+1},x{y+8}}),
TableFromRows = Table.FromRows(TransformMany,{"AccountName","AccountFullName","Class","Amount"}),
#"Filter<>0" = Table.SelectRows(TableFromRows, each [Amount] <> 0)
in
#"Filter<>0"

 

Stéphane

  • Hamster0406's avatar
    Hamster0406
    Frequent Visitor
    3 years ago

    Thank you for the solution and for teaching me 2 new formulas in Power Query M. I have worked on it some more and made it more dynamic so as to not hard code the column numbers.