Forum Discussion
Creating a Custom Table with DAX using Columns with Text Values
I am trying to create a custom table in Power BI using DAX. Each cell of the table consists of the value from a different field of the data source table I uploaded (like a SQL query that uses subquer...
Hi Anonymous ,
Here is full M query code that used transform table structures, you can try it if helps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc0hDoAwFATRq5Cva9gFjsAJcE0dFU2QFT0+OEgYNxnzco6jjj7NkWJv9TqfKuk3RdM0F5orze2dIl2ki3SRLtJFukk36SbdpJt0f/VyAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", type text}, {"Col 2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Col 1"}, {{"Cont", each List.Transform(List.Split(_[Col 2], List.Count(_[Col 2])/2),each Text.Combine(_,",")), type list}}), #"Expanded Cont" = Table.ExpandListColumn(#"Grouped Rows", "Cont"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Cont", "Cont", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Col 2", "Col 3", "Col 4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Col 1", type text}, {"Col 2", type text}, {"Col 3", type text}, {"Col 4", type text}}) in #"Changed Type1"Notice: this new table structure may not suitable to work with the solution that I provide above.
Regards,Xiaoxin Sheng
Thank you for the tip! I tried your solution, and it works to stack everything into one column. I'm having trouble trying to create three columns though. Right now my data looks like this:
| Col 1 | Col 2 |
| Text 1 | Field 1 |
| Text 1 | Field 2 |
| Text 1 | Field 3 |
| Text 1 | Field 4 |
| Text 1 | Field 5 |
| Text 1 | Field 6 |
| Text 2 | Field 1 |
| Text 2 | Field 2 |
| Text 2 | Field 3 |
| Text 2 | Field 4 |
| Text 2 | Field 5 |
| Text 2 | Field 6 |
| Text 3 | Field 1 |
| Text 3 | Field 2 |
| Text 3 | Field 3 |
| Text 3 | Field 4 |
| Text 3 | Field 5 |
| Text 3 | Field 6 |
Do you know how I can pivot it into three columns instead of one? More like this:
| Col 1 | Col 2 | Col 3 | Col 4 |
| Text 1 | Field 1 | Field 2 | Field 3 |
| Text 1 | Field 4 | Field 5 | Field 6 |
| Text 2 | Field 1 | Field 2 | Field 3 |
| Text 2 | Field 4 | Field 5 | Field 6 |
| Text 3 | Field 1 | Field 2 | Field 3 |
| Text 3 | Field 4 | Field 5 | Field 6 |
Hi Anonymous ,
Here is full M query code that used transform table structures, you can try it if helps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc0hDoAwFATRq5Cva9gFjsAJcE0dFU2QFT0+OEgYNxnzco6jjj7NkWJv9TqfKuk3RdM0F5orze2dIl2ki3SRLtJFukk36SbdpJt0f/VyAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", type text}, {"Col 2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Col 1"}, {{"Cont", each List.Transform(List.Split(_[Col 2], List.Count(_[Col 2])/2),each Text.Combine(_,",")), type list}}),
#"Expanded Cont" = Table.ExpandListColumn(#"Grouped Rows", "Cont"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Cont", "Cont", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Col 2", "Col 3", "Col 4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Col 1", type text}, {"Col 2", type text}, {"Col 3", type text}, {"Col 4", type text}})
in
#"Changed Type1"
Notice: this new table structure may not suitable to work with the solution that I provide above.
Regards,
Xiaoxin Sheng