Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
how can I seperate out value column information in different column.
Issiue is that all information is in random order.
Solved! Go to Solution.
@PSB Let me see what I can do. Power Query is a pain for this kind of thing sometimes compared to DAX.
@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"
@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)
Could you please provide Power Query solution. I have lot of other transformation to apply to this result.
@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"
@PSB Let me see what I can do. Power Query is a pain for this kind of thing sometimes compared to DAX.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |