Rename nested tables

[monojchakrab]

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.,

[BA_Pete · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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