Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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

 

 

Mahyartf

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

Mahyartf

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.