Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Trying to find away to combine two rows but the combination needs to have a offset as shown below.
Solved! Go to Solution.
Can be done in M @Jmiller
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8klNK1GK1QGzgzLTMyCc4ILUvJTMvHSgIFGSRJgSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spending = _t, #"Non-Spending" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Spending", type text}, {"Non-Spending", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Spending] = "Spending")), #"Inserted Subtraction" = Table.AddColumn(#"Filtered Rows", "Subtraction", each [Index] - 1, type number), #"Merged Queries" = Table.NestedJoin(#"Inserted Subtraction",{"Subtraction"},#"Added Index",{"Index"},"Inserted Subtraction",JoinKind.LeftOuter), #"Expanded Inserted Subtraction" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Subtraction", {"Non-Spending"}, {"Non-Spending.1"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Inserted Subtraction", "Merged", each Text.Combine({[Spending], [#"Non-Spending.1"]}, "+"), type text), #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Index", "Merged"}), #"Merged Queries1" = Table.NestedJoin(#"Added Index",{"Index"},#"Removed Other Columns",{"Index"},"Removed Other Columns",JoinKind.LeftOuter), #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Merged"}, {"Merged"}), #"Sorted Rows" = Table.Sort(#"Expanded Removed Other Columns",{{"Index", Order.Ascending}}) in #"Sorted Rows"
Can be done in M @Jmiller
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8klNK1GK1QGzgzLTMyCc4ILUvJTMvHSgIFGSRJgSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spending = _t, #"Non-Spending" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Spending", type text}, {"Non-Spending", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Spending] = "Spending")), #"Inserted Subtraction" = Table.AddColumn(#"Filtered Rows", "Subtraction", each [Index] - 1, type number), #"Merged Queries" = Table.NestedJoin(#"Inserted Subtraction",{"Subtraction"},#"Added Index",{"Index"},"Inserted Subtraction",JoinKind.LeftOuter), #"Expanded Inserted Subtraction" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Subtraction", {"Non-Spending"}, {"Non-Spending.1"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Inserted Subtraction", "Merged", each Text.Combine({[Spending], [#"Non-Spending.1"]}, "+"), type text), #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Index", "Merged"}), #"Merged Queries1" = Table.NestedJoin(#"Added Index",{"Index"},#"Removed Other Columns",{"Index"},"Removed Other Columns",JoinKind.LeftOuter), #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Merged"}, {"Merged"}), #"Sorted Rows" = Table.Sort(#"Expanded Removed Other Columns",{{"Index", Order.Ascending}}) in #"Sorted Rows"
Interesting. I will have to put some time into copying that over tonight. Not that advanced with PowerBI.
You should be able to combine them with the below function:
That works if the data is in the same row but not when the row is offset.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |