Forum Discussion
Creating a Custom Table with DAX using Columns with Text Values
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
Let's say this is the Data Source file (all dummy data - Field 1 & 6 Percentage format, Field 2 & 5 Decimal format, Field 3 & 4 Integer format):
| Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | Field 6 | Field 7 |
| 5.7% | 3.2 | 6 | 11 | 99.8 | 52% | A |
| 6.2% | 22.4 | 9 | 34 | 33.7 | 47% | B |
| 9.3% | 21.6 | 7 | 27 | 60.5 | 22% | C |
| 4.9% | 4.6 | 2 | 18 | 23.6 | 72% | D |
Then I want to create a table in DAX that takes on this format with the query:
Slicer set to: Field 7 = B
| Col 1 | Col 2 | Col 3 |
| "Text I add manually 1" | 6.2% | 22.4 |
| "Text I add manually 2" | 9 | 34 |
| "Text I add manually 3" | 33.7 | 47% |
As you can see, I'm stacking different data types into the same column this way which is causing the problem. The desired solution is a way to accomplish a table like this using some DAX function. Any help you can provide is greatly appreciated!
Hi Anonymous,
I'd like to suggest you do 'unpivot column' on the table fields and use Field 7' as group. Then you can use 'field 7' as 'Row', attribute as 'Column' and value as 'Value' to create matrix visuals.
Unpivot columns - Power Query | Microsoft Learn
You can filter on the attribute field to control displied fields and setting formatting string based on current attribute field value.
Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn
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