Adding custom rows in Power Query

[peterso]

Hi everyone,

I've got a bit of an interesting problem here that I can't seem to solve. Wondering if you may know the answer to this.

Based on each distinct "Assignment", I need to add two rows:

Row 1

"Assignment" row should reflect the current Assignment number

"Scopes" row should reflect Assignment.CustomText

"Labor" should reflect $0

"Materials" should reflect $0

"Rent" should be the SUM of rents for that particular assignment

Row 2

"Assignment" row should reflect the current Assignment number

"Scopes" row should reflect Assignment.AnotherCustomText

"Labor" should reflect $0

"Materials" should reflect $0

"Rent" should reflect $0

Thanks in advance!

DATA

EXPECTED RESULT

@harshnathani  - do you have any ideas on this?

[Jimmy801 · Accepted solution]

Hello @peterso 

you can apply Table.Group twice to your orignal data and then combine your source with the results of your Table.Groups

Here the code to understand it better

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrMq1QoSa0oATJNodjCQClWB0MWxDUD0UYQaSNUaWMgNgfTWCTBpoJooNZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Assignment = _t, Scopes = _t, Labor = _t, Materials = _t, Rent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Assignment", Int64.Type}, {"Scopes", type text}, {"Labor", Int64.Type}, {"Materials", Int64.Type}, {"Rent", Int64.Type}}),
    CreateCustomTextRow = Table.Group(#"Changed Type", {"Assignment"}, {{"Rent", each List.Sum([Rent]), type number}, {"Scopes", each Text.From([Assignment]{0})&".CustomText"}}),
    CreateAnotherCustomTextRow = Table.Group(#"Changed Type", {"Assignment"}, {{"Rent", each 0, type number}, {"Scopes", each Text.From([Assignment]{0})&".AnotherCustomText"}}),
    Combine = Table.Combine({#"Changed Type", CreateCustomTextRow, CreateAnotherCustomTextRow})
in
    Combine

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[peterso]

Hey Jimmy - I got a cyclical reference. Wondering why that is.

[Jimmy801]

Hello @peterso 

then probably is there a cyclical error 🙂

When you post your code, I can check it out

BR

Jimmy

[v-alq-msft]

Hi, @peterso 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Data:

You may create a blank query and paste the following codes in the corresponding 'Advanced Editor'.

let
    Source1 = Data[Assignment],
    #"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Custom1" = Table.Distinct(#"Converted to Table"),
    #"Renamed Columns" = Table.RenameColumns(Custom1,{{"Column1", "Assignment"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Scopes", each Text.From([Assignment])&".customtext"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Labor", each 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Materials", each 0),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Rent", each let 
assignment=[Assignment], 
tab = Table.SelectRows(Data,each [Assignment]=assignment),
sum = List.Sum(tab[Rent])
in 
sum),
    Source2 = Data[Assignment],
    #"Converted to Table2" = Table.FromList(Source2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Custom2" = Table.Distinct(#"Converted to Table2"),
    #"Renamed Columns2" = Table.RenameColumns(Custom2,{{"Column1", "Assignment"}}),
    #"Added Custom4" = Table.AddColumn(#"Renamed Columns2", "Scopes", each Text.From([Assignment])&".anothercustomtext"),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Labor", each 0),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Materials", each 0),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Rent", each 0),
    Source = Table.Combine({#"Added Custom3",#"Added Custom7"}),
    #"Sorted Rows" = Table.Sort(Source,{{"Assignment", Order.Ascending}}),
    #"Result" = Table.Combine({Data,#"Sorted Rows"})

in
    #"Result"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

Hi, there

Just unpivot such a table by columns "customtext" and "anothercustomertext", then append to the table "DATA".

[peterso]

I don't quite understand. Can you explain please?

[CNENFRNL]

Pls refer to the attached file  for the procedure.

[peterso]

There is a circular reference error.