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

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.

Reply
monojchakrab
Resolver III
Resolver III

Rename nested tables

Hey good people of Power query,

 

I have a table with a column holding nested tables as below :

 

monojchakrab_0-1689074152654.png

What I am trying to achieve is as follows :

  • If the [Account] = "Flipkart", rename two columns for the corresponding table in the [Data] column, else
  • leave the original table unchanged

Towards this, I have written the following code :

 

= Table.TransformColumns(RemovePharmEasy,{{"Data",if [Account]="Flipkart"
        then each Table.RenameColumns([Data],{{"Product Title/Description","Product Title"},{"Item Quantity","Item Qty"}}) 
else _}})

 

But unfortunately its returning an error as below :

 

Screenshot (147).png

Am I missing something? 

 

Any help much appreciated.

 

best regds.,

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @monojchakrab ,

 

You can do it fairly easily if you add your updated tables as a new column:

 

addNewDataTables = 
    Table.AddColumn(
        previousStep,
        "newData",
        each let __account = [Account] in
        if __account = "Flipkart"
        then Table.RenameColumns(
            [Data],
            {
                {"Product Title/Description", "Product Title"},
                {"Item Quantity", "Item Qty"}
            }
        )
        else [Data]
    )

 

Which gives the following outputs:

 

Non-Flipkart:

BA_Pete_0-1689087092037.png

 

Flipkart:

BA_Pete_1-1689087118507.png

 

Full example queries:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPU4rViVZyy8ksyE4sKgFzvDLzfWFshEQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t]),
    addData = Table.AddColumn(Source, "Data", each Table2),

    addNewDataTables = 
        Table.AddColumn(
            addData,
            "newData",
            each let __account = [Account] in
            if __account = "Flipkart"
            then Table.RenameColumns(
				[Data],
                {
					{"Product Title/Description", "Product Title"},
					{"Item Quantity", "Item Qty"}
				}
			)
			else [Data]
		)
in
    addNewDataTables

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5Pzi5W0lEys1SK1YlWCkjMKwFxTYwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Title/Description" = _t, #"Item Quantity" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Product Title/Description", type text}, {"Item Quantity", Int64.Type}})
in
    chgTypes

 

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

latimeria
Solution Specialist
Solution Specialist

Hi @monojchakrab ,

I would tackle the problem like this:

  • split the table RemovePharmEasy , account = flipkart and the other one account not = flikart
  • do your tranformation on the flipkart
  • merge back the 2 table
splitflipkart = Table.SelectRows(flipkart, each ([Account] = "flipchart"))
splitothers = Table.SelectRows(flipkart, each not ([Account] = "flipchart"))
transform= Table.TransformColumns(splitflipkart,  ....)
merge = Table.Combine(transform, splitaothers)

 

View solution in original post

2 REPLIES 2
latimeria
Solution Specialist
Solution Specialist

Hi @monojchakrab ,

I would tackle the problem like this:

  • split the table RemovePharmEasy , account = flipkart and the other one account not = flikart
  • do your tranformation on the flipkart
  • merge back the 2 table
splitflipkart = Table.SelectRows(flipkart, each ([Account] = "flipchart"))
splitothers = Table.SelectRows(flipkart, each not ([Account] = "flipchart"))
transform= Table.TransformColumns(splitflipkart,  ....)
merge = Table.Combine(transform, splitaothers)

 

BA_Pete
Super User
Super User

Hi @monojchakrab ,

 

You can do it fairly easily if you add your updated tables as a new column:

 

addNewDataTables = 
    Table.AddColumn(
        previousStep,
        "newData",
        each let __account = [Account] in
        if __account = "Flipkart"
        then Table.RenameColumns(
            [Data],
            {
                {"Product Title/Description", "Product Title"},
                {"Item Quantity", "Item Qty"}
            }
        )
        else [Data]
    )

 

Which gives the following outputs:

 

Non-Flipkart:

BA_Pete_0-1689087092037.png

 

Flipkart:

BA_Pete_1-1689087118507.png

 

Full example queries:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPU4rViVZyy8ksyE4sKgFzvDLzfWFshEQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t]),
    addData = Table.AddColumn(Source, "Data", each Table2),

    addNewDataTables = 
        Table.AddColumn(
            addData,
            "newData",
            each let __account = [Account] in
            if __account = "Flipkart"
            then Table.RenameColumns(
				[Data],
                {
					{"Product Title/Description", "Product Title"},
					{"Item Quantity", "Item Qty"}
				}
			)
			else [Data]
		)
in
    addNewDataTables

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5Pzi5W0lEys1SK1YlWCkjMKwFxTYwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Title/Description" = _t, #"Item Quantity" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Product Title/Description", type text}, {"Item Quantity", Int64.Type}})
in
    chgTypes

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors