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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Zyg_D
Continued Contributor
Continued Contributor

Transforming 2 columns at once in powerquery-m

This is my data: 2 original columns + 1 made from the first one. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSo0pNTAwMlO2NDWzVshNLcnIT1HSUYIJmphZ6xaXFJUml5QWpSrF6kQrJefnFZck5pUowJSYGVgja7CwNjR2BhqUWAzUkZuaV6IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}}),
    HtmlTable = Table.AddColumn(#"Changed Type", "HtmlTable", each Html.Table([col1],{{"HtmlDecoded",":root"}})),
    #"Expanded HtmlTable" = Table.ExpandTableColumn(HtmlTable, "HtmlTable", {"HtmlDecoded"}, {"HtmlDecoded"})
in
    #"Expanded HtmlTable"

data.png 

I cannot find a way to transform 2 columns at once. Is there one?

1 ACCEPTED SOLUTION
Zyg_D
Continued Contributor
Continued Contributor

Thank you, @mahoneypat .
Using your answer I was able to create what I needed. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSo0pNTAwMlO2NDWzVshNLcnIT1HSUYIJmphZ6xaXFJUml5QWpSrF6kQrJefnFZck5pUowJSYGVgja7CwNjR2BhqUWAzUkZuaV6IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}}),
    Decoded = Table.TransformColumns(#"Changed Type", {
        {"col1", each Table.FirstValue(Html.Table(_,{{"HtmlDecoded1",":root"}}))}, 
        {"col2", each Table.FirstValue(Html.Table(_,{{"HtmlDecoded2",":root"}}))} } )
in
    Decoded

 

View solution in original post

2 REPLIES 2
Zyg_D
Continued Contributor
Continued Contributor

Thank you, @mahoneypat .
Using your answer I was able to create what I needed. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSo0pNTAwMlO2NDWzVshNLcnIT1HSUYIJmphZ6xaXFJUml5QWpSrF6kQrJefnFZck5pUowJSYGVgja7CwNjR2BhqUWAzUkZuaV6IUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}}),
    Decoded = Table.TransformColumns(#"Changed Type", {
        {"col1", each Table.FirstValue(Html.Table(_,{{"HtmlDecoded1",":root"}}))}, 
        {"col2", each Table.FirstValue(Html.Table(_,{{"HtmlDecoded2",":root"}}))} } )
in
    Decoded

 

mahoneypat
Microsoft Employee
Microsoft Employee

Yes.  The Table.TransformColumns function takes a list of lists as an argument, each of which is a column name and the function to be applied.  In below, I took your M, highlighted your first two columns and chose lowercase on the Transform tab.  This code can now be changed to do different transform to each column.

 

= Table.TransformColumns(#"Expanded HtmlTable",{{"col1", Text.Lower, type text}, {"col2", Text.Lower, type text}})

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors