Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Atf23
Frequent Visitor

Json format of each record

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1713279040647.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

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:

BA_Pete_0-1713279040647.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors