Split Column type in Power Query Editor

[MF_BI]

Hello all,

I have a table in Power Query like this one below :

Type	value
Duration	PT10M
Integer	8
Durationr	PT13H
Durationr	PT30M
Durationr	PT50S
Integer	14

and I want to Split the column Value in two columns : Value duration and Value integer depended from type like this table below : 

Type	value	value duration	value integer
Duration	PT10M	PT10M
Integer	8		8
Durationr	PT13H	PT13H
Durationr	PT30M	PT30M
Durationr	PT50S	PT50S
Integer	14		14

Any one have Idea how I can do that ? Thank you.

Best,

[Anonymous]

Hi @MF_BI ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd · Accepted solution]

Hey @MF_BI ,

yes, you can do that by checking the format of the row with Value.Is and Value.FromText.

Here my full example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciktSizJzM9T0lEKCDE08FWK1YlW8swrSU1PLQKKWYD5MEVFEFXGHlhEjaF6UUVNDYLRTDQ0UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, value = _t]),
    #"Added Custom" = Table.AddColumn(Source, "value integer.1", each if Value.Is(Value.FromText( [value] ),type number) 
then Value.FromText( [value] )
else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "value duration", each if not Value.Is(Value.FromText( [value] ),type number) 
then [value]
else null)
in
    #"Added Custom1"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic