Forum Discussion

AngelikaMcGill's avatar
AngelikaMcGill
New Member
8 years ago
Solved

Combining data from various columns into one column based on value in another column

Hopefully this isn't as complicated as it seems...   I am extracting data from Dynamics and in the extraction I have values that are coming into power bi in various columns instead of one based on ...
Need Help
v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
8 years ago

AngelikaMcGill

 

You can unpivot part of the columns as below in Query Editor.

 

 

Then create relationship between two tables using the First Type column.

 

 

I create a Tag column to indentify the answer using following DAX formula.

 

Tag = 
IF (
    Table1[First Type] = "ShortText",
    SEARCH ( "Text", Table1[Attribute],, -1 ),
    IF (
        Table1[First Type] = "Table",
        SEARCH ( "First GroupAnswer2", Table1[Attribute],, -1 ),
        IF (
            Table1[First Type] = "MultipleRadio"
                || Table1[First Type] = "MultipleCheckbox",
            SEARCH ( "First Checkbox", Table1[Attribute],, -1 ),
            SEARCH ( Table1[First Type], Table1[Attribute],, -1 )
        )
    )
)

 

At last, we only need to use the Tag column to get the desired answer.

 

Answer = 
CALCULATE ( MAX ( Table1[Value] ), Table1[Tag] > 0 )

 

Best Regards,
Herbert