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 there,
I have some data which is structured like the below table, where columns 3, 4 and 5 contain lists of values separated by a delimiter:
Project | Project Location | Step Number | Step Detail | Step Owner |
Project A | Townsville | 1, 2, 3, 4 | "Make a plan", "Check the plan", "Do the plan", "Review the plan" | "Bob", "Greg", "Dave", "Bob" |
Project B | Cityberg | 1, 2, 3 | "Construct building", "Use building", "Deconstruct building" | "Anna", "Jane", "Steph" |
Project C | Villagetown | 1, 2, 3 | "Write a book", "Publish a book", "Sell the book" | "Sam", "Chris", "Taylor" |
I would like to split these columns into the number of rows as there are elements in these lists, resulting in something that looks like this:
Project | Project Location | Step Number | Step Detail | Step Owner |
Project A | Townsville | 1 | "Make a plan" | "Bob" |
Project A | Townsville | 2 | "Check the plan" | "Greg" |
Project A | Townsville | 3 | "Do the plan" | "Dave" |
Project A | Townsville | 4 | "Review the plan" | "Bob" |
Project B | Cityberg | 1 | "Construct building" | "Anna" |
Project B | Cityberg | 2 | "Use building" | "Jane" |
Project B | Cityberg | 3 | "Deconstruct building" | "Steph" |
Project C | Villagetown | 1 | "Write a book" | "Sam" |
Project C | Villagetown | 2 | "Publish a book" | "Chris" |
Project C | Villagetown | 3 | "Sell the book" | "Taylor" |
using Power Query or DAX. Is this possible?
Solved! Go to Solution.
Here is an example of how to do this in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNCsIwEIRfJeSciz8v0FYQBEGsPwfrYVOXNjYmkqQtvr0xqajF2+7s7PAxpxPdGH3F0pGEMrrTvbKdkBL9MmFkysiMkblfCrqGBgmQuwRVUEYKmtVYNsTV+KUt9EjYYiew/xJDVqp5PC8NVsMndBincKRn9kFL/VMm3IOjqT5gISnTyjrTehNvhbwINcTtLY6UBZZ/vCEkUQqiaQVqoMgd3usRR+bdB98OVOh8UyOUoxHu1RDXuokZm5ZLYesfLUcpQx1RCp853N6VGmHjuIOH1OYFcH4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Location" = _t, #"Step Number" = _t, #"Step Detail" = _t, #"Step Owner" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Location", type text}, {"Step Number", type text}, {"Step Detail", type text}, {"Step Owner", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Step Detail], ","), Text.Split([Step Owner], ",")})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Step Detail", "Step Owner"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "Step Detail"}, {"Custom.2", "Step Owner"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Step Detail", Text.Trim, type text}, {"Step Owner", Text.Trim, type text}})
in
#"Trimmed Text"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is an example of how to do this in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNCsIwEIRfJeSciz8v0FYQBEGsPwfrYVOXNjYmkqQtvr0xqajF2+7s7PAxpxPdGH3F0pGEMrrTvbKdkBL9MmFkysiMkblfCrqGBgmQuwRVUEYKmtVYNsTV+KUt9EjYYiew/xJDVqp5PC8NVsMndBincKRn9kFL/VMm3IOjqT5gISnTyjrTehNvhbwINcTtLY6UBZZ/vCEkUQqiaQVqoMgd3usRR+bdB98OVOh8UyOUoxHu1RDXuokZm5ZLYesfLUcpQx1RCp853N6VGmHjuIOH1OYFcH4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Location" = _t, #"Step Number" = _t, #"Step Detail" = _t, #"Step Owner" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Location", type text}, {"Step Number", type text}, {"Step Detail", type text}, {"Step Owner", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Step Detail], ","), Text.Split([Step Owner], ",")})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Step Detail", "Step Owner"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "Step Detail"}, {"Custom.2", "Step Owner"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Step Detail", Text.Trim, type text}, {"Step Owner", Text.Trim, type text}})
in
#"Trimmed Text"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This is just what I was after, thank you
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |