Forum Discussion

peterso's avatar
peterso
Icon for Helper II rankHelper II
6 years ago
Solved

Adding 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 row...
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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.