Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have a csv file with a huge number of records, when loaded into powe query, I need to add a new custom column that contains the json representation of each record as follows :
col1, col2, col3, json
A, 1, c, {"col1":"A","col2":1,"col3":"c"}
B, 2, d, {"col1":"B","col2":2,"col3":"d"}
thanks
Solved! Go to Solution.
Hi @Atf23 ,
Try this example query by pasting into a new blank query in Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4mSlWJ1oJScgywiIU5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
addJson =
Table.AddColumn(
Source,
"json",
each Text.FromBinary(
Json.FromValue(
[
[Col1],
[Col2],
[Col3]
]
)
)
)
in
addJson
Example query output:
To add as a custom column to your existing query, just use this bit of the code in the column formula:
Text.FromBinary(
Json.FromValue(
[
[Col1],
[Col2],
[Col3]
]
)
)
Pete
Proud to be a Datanaut!
Hi @Atf23 ,
Try this example query by pasting into a new blank query in Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4mSlWJ1oJScgywiIU5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
addJson =
Table.AddColumn(
Source,
"json",
each Text.FromBinary(
Json.FromValue(
[
[Col1],
[Col2],
[Col3]
]
)
)
)
in
addJson
Example query output:
To add as a custom column to your existing query, just use this bit of the code in the column formula:
Text.FromBinary(
Json.FromValue(
[
[Col1],
[Col2],
[Col3]
]
)
)
Pete
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |