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.
Here's my current dataset:
Column 1 | Column 2 |
Customer Name1 | aaa |
Street Name1 | bbb |
City Name1 | ccc |
Country1 | ddd |
Customer Name2 | eee |
Street Name2 | fff |
City Name2 | ggg |
Country2 | hhh |
I want it to look like this:
Column 1 | Column 2 |
Customer Name1, Street Name1, City Name1, Country1 | aaa |
Customer Name1, Street Name1, City Name1, Country1 | bbb |
Customer Name1, Street Name1, City Name1, Country1 | ccc |
Customer Name1, Street Name1, City Name1, Country1 | ddd |
Customer Name2, Street Name2, City Name2, Country2 | eee |
Customer Name2, Street Name2, City Name2, Country2 | fff |
Customer Name2, Street Name2, City Name2, Country2 | ggg |
Customer Name2, Street Name2, City Name2, Country2 | hhh |
Solved! Go to Solution.
Hi @Anonymous
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"})
in
#"Removed Other Columns"
Hi @rblaze21
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"})
in
#"Removed Other Columns"
Hi @Anonymous
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"})
in
#"Removed Other Columns"
Thank you so much!
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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |