Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
3 years ago
Solved

Rename nested tables

Hey good people of Power query,

 

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

 

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 :

 

Am I missing something? 

 

Any help much appreciated.

 

best regds.,

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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:

     

    Flipkart:

     

    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

  • latimeria's avatar
    latimeria
    3 years ago

    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)

     

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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:

     

    Flipkart:

     

    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

  • latimeria's avatar
    latimeria
    Solution Specialist
    3 years ago

    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)