Forum Discussion
Helper IIAdding custom rows in Power Query
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
Anonymous - do you have any ideas on this?
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 CombineCopy 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
8 Replies
Jimmy801Community Champion
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 CombineCopy 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
CNENFRNLHi, 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.
v-alq-msftCommunity Support
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.
