CODEWITHSUNDEEP
powerbi
Vine
Vine

Reputation: 395

Unpivot Data and Separate Integers and Strings in Power Bi

enter image description here

Above is my current input.

Below is the desired output.

enter image description here

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?

enter image description here

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"

Upvotes: 1

Views: 253

Answers (1)

exan
exan

Reputation: 3925

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))

Upvotes: 1

Related Questions