Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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.
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
Thank you for your reply. I went with the other solution as it was easier for me to understand and modify.