Unpivot Data and Separate Integers and Strings in Power Bi

Question

Above is my current input.

Below is the desired output.

Currently, i load the data into Power Bi, and using the Power Query Editor I 'unpivot columns' on 'selected columns only'. This results in a column of mixed data types (Integers and strings). Is anyone able to advise an efficient method upon data load how to separate string values and integers?

Below is the code from Advanced Editor

let
Source = Csv.Document(File.Contents("F:\Surveys\dev\pivottest.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"responseid", Int64.Type}, {"q1", Int64.Type}, {"q2", type text}, {"q3", Int64.Type}, {"q4", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"q1", "q2", "q3", "q4"}, "Attribute", "Value") 
in
#"Unpivoted Only Selected Columns"

exan exan · Accepted Answer · 2019-11-20T01:30:03

After you have unpivoted your data, then use the function Value.Is on your Value column. To check if a value is number, it would look something like this:

Value.Is([Column], Int64.Type)

Example, 
IsNumber =Value.Is(Value.FromText([ColumnOfMixedValues]), type number)

To check for a text, it would be:

Value.Is([Column],type text)

Example,
IsText =Value.Is(Value.FromText([ColumnOfMixedValues]), type text)

Value.Is returns a boolean true/false, so you can wrap it in an IF.

if Value.Is(AlphaNumeric, type text) then AlphaNumeric else Number.ToText(AlphaNumeric))

Unpivot Data and Separate Integers and Strings in Power Bi

1 Answers