Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My source data table, when imported, has a table column containing a list of values, such as [Store_ID] in the example below:
[Store_Owner], [Store_ID]
Owner A, {10001, 10002, 10003}
Owner B, {10004}
Owner C, {10005, 10006}
How does one unlist the [Store_ID] values and reshape the data into a "long" format table, either during table import and/or using DAX, resulting in the following long table format:
[Store_Owner], [Store_ID]
Owner A, 10001
Owner A, 10002
Owner A, 10003
Owner B, 10004
Owner C, 10005
Owner C, 10006
Solved! Go to Solution.
The reply by MFelix led me to a more simplified solution: In the Power Query Editor, use Split Column by Delimiter and Split into Rows (found in the Advanced options) instead of splitting into columns.
This transform operation directly produced the "long" format table I needed.
Hi @Wing,
For your requirement, dax may not achieve that, M query would be better.
Assuming that you have the table like this when you import data in Query Editor.
If you want to get the table like this.
[Store_Owner], [Store_ID]
Owner A, 10001
Owner A, 10002
Owner A, 10003
Owner B, 10004
Owner C, 10005
Owner C, 10006
You only need expand the list to new rows with click the option highlight in yellow.
Best Regards,
Cherry
Hi Cherry, I later realized my [Store_ID] column is just a text field with commas, and not a bona fide list (didn't initially recognize this as a Power BI newbie).
So I used Split Column by Delimiter and Split into Rows to get the "long" format table I needed.
I will keep your solution in mind in situations involving lists.
Thank you.
Hi @Wing,
On the query Editor do the following:
See M language coding:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUao2NDAwMNRRAFFGEMq4VilWJ1rJCSZrAuE7w/imEGVmQOFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Store Owner" = _t, #"Store ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Owner", type text}, {"Store ID", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"Store ID"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"Store ID"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Store ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Store ID.1", "Store ID.2", "Store ID.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Store ID.1", Int64.Type}, {"Store ID.2", Int64.Type}, {"Store ID.3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Store Owner"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Store ID"}}) in #"Renamed Columns"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe reply by MFelix led me to a more simplified solution: In the Power Query Editor, use Split Column by Delimiter and Split into Rows (found in the Advanced options) instead of splitting into columns.
This transform operation directly produced the "long" format table I needed.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |