Forum Discussion
Peavey
Helper III
Helper IIISum a row based on content in columns
Hello,
I need to sum the different variables into columns, see table below. How can I sum for each type?
Hi Peavey ,
Here I create a sample to have a test.
M Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQqpLEhVMAIyjJA5JjAOSImhUqxONELeGMgwRZZHkTEGKzZG1Y/EMUVWbKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Var1 = _t, #"Amount 1" = _t, Var2 = _t, #"Amount 2" = _t, Var3 = _t, #"Amount 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Var1", type text}, {"Amount 1", Int64.Type}, {"Var2", type text}, {"Amount 2", Int64.Type}, {"Var3", type text}, {"Amount 3", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Amount 1", type text}}, "en-US"),{"Var1", "Amount 1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 1 - Amount 1"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Amount 2", type text}}, "en-US"),{"Var2", "Amount 2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 2 - Amount 2"), #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Amount 3", type text}}, "en-US"),{"Var3", "Amount 3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 3 - Amount 3"), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"ID", "Attribute.1", "Value", "Attribute.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.2", "Amount"}, {"Attribute.1", "Var"}, {"Value.1", "Type"}}) in #"Renamed Columns"Result is as below.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
StrategicSavvyResolver II
Hi ,
You should structure your data at the beginning.
Try to structure in this pattern:
VarNumber | Type | Amount
Var1 | Type 2 | 2
Once you have this structure you can achieve this requirement by writing two measures:
1. Sum Type 1 = CALCULATE( SUM(Your Table [Amount]), Your Table[Type] = "Type 1")
2. Sum Type 1 = CALCULATE( SUM(Your Table [Amount]), Your Table[Type] = "Type 2")
Hope it helps!
Hi Peavey ,
Here I create a sample to have a test.
M Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQqpLEhVMAIyjJA5JjAOSImhUqxONELeGMgwRZZHkTEGKzZG1Y/EMUVWbKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Var1 = _t, #"Amount 1" = _t, Var2 = _t, #"Amount 2" = _t, Var3 = _t, #"Amount 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Var1", type text}, {"Amount 1", Int64.Type}, {"Var2", type text}, {"Amount 2", Int64.Type}, {"Var3", type text}, {"Amount 3", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Amount 1", type text}}, "en-US"),{"Var1", "Amount 1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 1 - Amount 1"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Amount 2", type text}}, "en-US"),{"Var2", "Amount 2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 2 - Amount 2"), #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Amount 3", type text}}, "en-US"),{"Var3", "Amount 3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Var 3 - Amount 3"), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"ID", "Attribute.1", "Value", "Attribute.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute.2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.2", "Amount"}, {"Attribute.1", "Var"}, {"Value.1", "Type"}}) in #"Renamed Columns"Result is as below.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.