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.
Hi,
I have data in the following format:
Name | File | Location |
Test 1 | a;b;c | 1;2;3 |
Test 2 | d | 4 |
Test 3 | e;f | 5;6 |
I'd like to expand the rows, so that each row contains a Name, File, Location match. For example:
Name | File | Location |
Test 1 | a | 1 |
Test 1 | b | 2 |
Test 1 | c | 3 |
Test 2 | d | 4 |
Test 3 | e | 5 |
Test 3 | f | 6 |
It would be even better if this was possible:
Name | File | Location |
Test 1.1 | a | 1 |
Test 1.2 | b | 2 |
Test 1.3 | c | 3 |
Test 2 | d | 4 |
Test 3.1 | e | 5 |
Test 3.2 | f | 6 |
Does anyone know how this can be done? Thank you all very much for your time.
- Daniel
Solved! Go to Solution.
I'd do it in the query editor, see example below (create a new query and paste the code from below in the Advanced Editor), you can examine the steps in the transformation in the Query Settings pane on the right of the Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRSrROsk4G0obWRtbGSrE6UCkjoFAKEJsghIyB3FTrNCBpam2mFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"File " = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"File ", type text}, {"Location", type text}}), FileToList = Table.TransformColumns(#"Changed Type",{{"File ", each Text.SplitAny(_, ";")}}), LocationToList = Table.TransformColumns(FileToList,{{"Location", each Text.SplitAny(_, ";")}}), ZipFileAndLocation = Table.AddColumn(LocationToList, "Custom", each List.Zip({[#"File "],[Location]})), #"Removed Columns" = Table.RemoveColumns(ZipFileAndLocation,{"File ", "Location"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"File", "Location"}) in #"Split Column by Delimiter"
I'd do it in the query editor, see example below (create a new query and paste the code from below in the Advanced Editor), you can examine the steps in the transformation in the Query Settings pane on the right of the Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRSrROsk4G0obWRtbGSrE6UCkjoFAKEJsghIyB3FTrNCBpam2mFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"File " = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"File ", type text}, {"Location", type text}}), FileToList = Table.TransformColumns(#"Changed Type",{{"File ", each Text.SplitAny(_, ";")}}), LocationToList = Table.TransformColumns(FileToList,{{"Location", each Text.SplitAny(_, ";")}}), ZipFileAndLocation = Table.AddColumn(LocationToList, "Custom", each List.Zip({[#"File "],[Location]})), #"Removed Columns" = Table.RemoveColumns(ZipFileAndLocation,{"File ", "Location"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"File", "Location"}) in #"Split Column by Delimiter"
That's brilliant. 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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |