Creating a Custom Table with DAX using Columns with Text Values

[RNd32m6]

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 subqueries to create the table) when it is filtered to a specific value in the main column. One of the columns I need to create includes percentages, whole numbers, and decimals. That is why I turned them all into text fields because (from what I understand) Power BI cannot create a custom table with a column that doesn't have a uniform data type. I have tried several different ways to create this table in DAX, but I am finding that every function for creating a custom table will not allow you to use columns of text values. You can add columns with scaler values, but if you use text columns, it throws an error telling you it requires a scaler column. Is there any way around this in Power BI? Can I create a custom table using the values from text fields?

The table I want to create has the following structure:

Filter: Field 7 from Data Source = "A"

Col 1                                                        Col 2                                      Col 3                                 

Manual Text                                             Field 1 from Data Source      Field 2 from Data Source

Manual Text                                             Field 3 from Data Source      Field 4 from Data Source

Manual Text                                             Field 5 from Data Source      Field 6 from Data Source

[RNd32m6]

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!

[v-shex-msft]

Hi @RNd32m6,

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

[RNd32m6]

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

[v-shex-msft · Accepted solution]

Hi @RNd32m6 ,

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

[v-shex-msft]

Hi @RNd32m6 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng