cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shakedb70
Frequent Visitor

sum up a table by a column list

Hi!

Im kind of new to power bi and I have two tables as such:

shakedb70_0-1659901845700.png                                     image.png

Im trying to make a new column in the first table that sums up all the nums of the indexes in each list from the second table.

So the first table in this example will look like this:

shakedb70_3-1659902649445.png

Pay attention that the list is written as text.

I have tried to do so as a measure since the second table might change according to a different column that doesn't relates to this. 

Please if anyone can help me, I can really use the help

1 ACCEPTED SOLUTION
liuqi_pbi
Resolver II
Resolver II

Hi @shakedb70 

 

Here is my solution with Power Query. Hope it would be helpful!

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYo2iFWK1YlWcgKxDSFsZxDbCMJ2AbGNIWxXENsEwnYDq9cx0oHKuYPN0jHUAcnHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, indexes = _t]),
  #"Duplicated column" = Table.DuplicateColumn(Source, "indexes", "indexes - Copy"),
  #"Extracted text between delimiters" = Table.TransformColumns(#"Duplicated column", {{"indexes - Copy", each Text.BetweenDelimiters(_, "[", "]", 0, 0), type text}}),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted text between delimiters", {{"indexes - Copy", Splitter.SplitTextByDelimiter(","), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "indexes - Copy"),
  #"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter", {{"indexes - Copy", Int64.Type}}),
  #"Merged queries" = Table.NestedJoin(#"Changed column type", {"indexes - Copy"}, RefTable, {"index"}, "RefTable", JoinKind.LeftOuter),
  #"Expanded RefTable" = Table.ExpandTableColumn(#"Merged queries", "RefTable", {"num"}, {"num"}),
  #"Grouped rows" = Table.Group(#"Expanded RefTable", {"name"}, {{"indexes", each List.Max([indexes]), type nullable text}, {"sum", each List.Sum([num]), type nullable number}}),
  #"Sorted rows" = Table.Sort(#"Grouped rows", {{"name", Order.Ascending}})
in
  #"Sorted rows"

liuqi_pbi_0-1660212027175.png

 

----------------------------------------------------------------------

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

View solution in original post

4 REPLIES 4
liuqi_pbi
Resolver II
Resolver II

Hi @shakedb70 

 

Here is my solution with Power Query. Hope it would be helpful!

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYo2iFWK1YlWcgKxDSFsZxDbCMJ2AbGNIWxXENsEwnYDq9cx0oHKuYPN0jHUAcnHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, indexes = _t]),
  #"Duplicated column" = Table.DuplicateColumn(Source, "indexes", "indexes - Copy"),
  #"Extracted text between delimiters" = Table.TransformColumns(#"Duplicated column", {{"indexes - Copy", each Text.BetweenDelimiters(_, "[", "]", 0, 0), type text}}),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted text between delimiters", {{"indexes - Copy", Splitter.SplitTextByDelimiter(","), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "indexes - Copy"),
  #"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter", {{"indexes - Copy", Int64.Type}}),
  #"Merged queries" = Table.NestedJoin(#"Changed column type", {"indexes - Copy"}, RefTable, {"index"}, "RefTable", JoinKind.LeftOuter),
  #"Expanded RefTable" = Table.ExpandTableColumn(#"Merged queries", "RefTable", {"num"}, {"num"}),
  #"Grouped rows" = Table.Group(#"Expanded RefTable", {"name"}, {{"indexes", each List.Max([indexes]), type nullable text}, {"sum", each List.Sum([num]), type nullable number}}),
  #"Sorted rows" = Table.Sort(#"Grouped rows", {{"name", Order.Ascending}})
in
  #"Sorted rows"

liuqi_pbi_0-1660212027175.png

 

----------------------------------------------------------------------

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

MahyarTF
Solution Sage
Solution Sage

1- After Loading the Table in Power Query, Create a copy of the first table(included Name, Indexex) , do the below Step by step :

#"Changed Type" = Table.TransformColumnTypes(Sheet67_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Indexes", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Trim( Text.Trim([Indexes],"["), "]")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Indexes"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Indexes"),
#"Changed Type3" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Indexes", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Attribute"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"Indexes"}, Sheet68, {"Index"}, "Sheet68", JoinKind.LeftOuter),
#"Expanded Sheet68" = Table.ExpandTableColumn(#"Merged Queries", "Sheet68", {"Num"}, {"Sheet68.Num"})

2- Load it to Power BI

3- Develop your Visual :

MahyarTF_0-1659927925108.png

 

 

Hi!

thanks for the help but unfortunately i have indexes lists in all kind of lengths so doing it with max 3 columns wouldn't work for me.

Hi, 

For solving the issue of the column (variable count), please see the below video :

https://www.youtube.com/watch?v=bKkXxbOnqxA

Thanks for Kudos

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.