Forum Discussion
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 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.
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
ExpandRecordlet
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
4 Replies
slorinSuper User
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
ExpandRecordlet
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
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_recThank you for your reply. I went with the other solution as it was easier for me to understand and modify.
