text to column with condition

[PSB]

how can I seperate out value column information in different column.

Issiue is that all information is in random order.

[Greg_Deckler · Accepted solution]

@PSB Let me see what I can do. Power Query is a pain for this kind of thing sometimes compared to DAX.

[Greg_Deckler]

@PSB Here is some DAX for getting the Gnbid column. You should be able to easily replicated for the others.

Gnbid = 
    VAR __gNBid = SEARCH("gNBid=",[Value],,0)
    VAR __Start = SEARCH("=",[Value],__gNBid,0)
    VAR __EndTemp = SEARCH(",",[Value],__gNBid,0)
    VAR __End = IF(__EndTemp = 0, LEN([Value]),__EndTemp)
RETURN
    MID([Value],__Start + 1, __End - __Start - 1)

[PSB]

Could you please provide Power Query solution. I have lot of other transformation to apply to this result.

[Greg_Deckler · Accepted solution]

@PSB OK, this wasn't so bad:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZKxDoIwEIZfhXTu0OsdLR0Y0AmjxLASBkWCJo0kLr6+GKGAhKFhvBu++++7Kwp2yg5CGYJEMs6SoQDoKvvO6kdzv7avvLZdHTTZLr3FgIQSJQ+q2tpjW11s10QBPHjm530ah6RYyb3IcxIOJBUaPh/qC/5xADQPHEgiCbVI70secURGr8qQ4C3DkZEoCv/JTg5G2yxLZ1mriR1CZYR35H5ZhPFeUocGt2tebO+CgtBRF7x/Ou39GysX00ZOdXynsLL8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_NodeId = _t, S_Cell = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S_NodeId", type text}, {"S_Cell", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Value], "gNBId=", ","), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "Gnbid"}}),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Value], "cellLocalId=", ", "), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Text Between Delimiters", "CellLocalID"}}),
    #"Inserted Text Between Delimiters2" = Table.AddColumn(#"Renamed Columns1", "Text Between Delimiters", each Text.BetweenDelimiters([Value], "nRPCI=", ","), type text)
in
    #"Inserted Text Between Delimiters2"

[Greg_Deckler · Accepted solution]

@PSB Let me see what I can do. Power Query is a pain for this kind of thing sometimes compared to DAX.