sum up a table by a column list

[shakedb70]

Hi!

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

                                     

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:

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

[liuqi_pbi · Accepted solution]

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"

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

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

[MahyarTF]

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 :

[shakedb70]

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.

[MahyarTF]

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