PQ transformation with label and values in different columns

[Hamster0406]

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 gets paired with Amount_1 and so on

Link to sample file: Class PL Sample.xlsx

Sample input

Sample output

There is no way to change the output at the software's end and the only option is to transform in PQ.

Please help with how the data transformation can be completed in PQ.

[slorin · Accepted solution]

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]

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.

[AlienSx]

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]

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