Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hamster0406
Regular Visitor

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

 

QBD Class Input.png

 

Sample output

QBD Class Output.png

 

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.

1 ACCEPTED SOLUTION
slorin
Super User
Super 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
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

View solution in original post

4 REPLIES 4
slorin
Super User
Super 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
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.

AlienSx
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors