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

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    data = List.Buffer(Table.ToRows(Source)),
    f = (lst as list) =>
        [first = List.Buffer(List.FirstN(lst, 3)),
        columns = List.Count(lst),
        b = (columns - 3) / 2,
        last = List.Buffer(List.LastN(lst, columns - 3)),
        d = List.Buffer({0..(b - 1)}),
        e = List.Accumulate(d, {}, (s, c) => s & (if last{c + b} = 0 then {} else { [Class = last{c}, Amount = last{c + b}] } )),
        g = first & {e}][g],
    txform = Table.FromRows(List.Transform(data, f), List.FirstN(Table.ColumnNames(Source), 3) & {"new_cols"}),
    expand_lst = Table.ExpandListColumn(txform, "new_cols"),
    expand_rec = Table.ExpandRecordColumn(expand_lst, "new_cols", {"Class", "Amount"})
in
    expand_rec
Hamster0406's avatar
Hamster0406
Frequent Visitor
3 years ago

Thank you for your reply. I went with the other solution as it was easier for me to understand and modify.