Power Query Editor, how to create parse mixed data column to custom column

[WillWullems]

Hi all,

I am new to Power Query, and I am having some issues in processing a text file.
There is a column that contains both numbers and text.
All numbers in this column are customers numbers.
All records belong to the same customer (until a record starts with a different customer number)

I would like to create a custom column for all records.
In "normal" excel, I would just use an if, combined with isnumber.

I tried using the conditional column, but I am a bit puzzled on how to set the criteria.

Thanks in advance.

 

[MarcelBeug]

You can use Value.Type and Type.Is to check for types, e.g.:

= if Type.Is(Value.Type([Value]), type text) then "Text" else if Type.Is(Value.Type([Value]), type number) then "Number" else "Neither text nor number"

 

[WillWullems]

Thanks for your reply.

When reading my question, I understand I was a bit unclear.
I see how the calculated column takes the value when available.
When the mixed data is text, I would like to repeat the number from the previous record (rather then "Text").

 

[MarcelBeug]

Edit: this is still a reaction to the original question.

Even shorter: you can use Value.Is:

= if Value.Is([Value], type text) then "Text" else if Value.Is([Value], type number) then "Number" else "Neither text nor number"

 

[MarcelBeug]

As a reaction to the updated question, this will work:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Custom column", each if Value.Is([Value], type number) then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom column"})
in
    #"Filled Down"

By the way: "Calculated Column" suggests a DAX solution rather than my Power Query solution with a "custom column".
Just to avoid misunderstandings.

 

[billszysz]

Hi Marcel,
We can directly use "is" operator in the query (will be shorter)

let
     Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Custom column", each if [Value] is number then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom column"})
in
    #"Filled Down"

Regards

 

[MarcelBeug]

Hi Bill,

Sure, thanks! I have been busy documenting comparer functions lately (and parameters "comparer", "equationCriteria" and "comparisonCriteria"), so I completely overlooked the obvious "is".
Well, at least that makes this a high educational topic, with the alternatives presented.

Cheers!