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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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