Forum Discussion

shakedb70's avatar
shakedb70
Frequent Visitor
4 years ago
Solved

sum up a table by a column list

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 index...
Need Help
  • liuqi_pbi's avatar
    liuqi_pbi
    4 years ago

    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's avatar
MahyarTF
Icon for Memorable Member rankMemorable Member
4 years ago

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's avatar
    shakedb70
    Frequent Visitor
    4 years ago

    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.