Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 :
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.,
Solved! Go to 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
Proud to be a Datanaut!
Hi @monojchakrab ,
I would tackle the problem like this:
splitflipkart = Table.SelectRows(flipkart, each ([Account] = "flipchart"))
splitothers = Table.SelectRows(flipkart, each not ([Account] = "flipchart"))
transform= Table.TransformColumns(splitflipkart, ....)
merge = Table.Combine(transform, splitaothers)
Hi @monojchakrab ,
I would tackle the problem like this:
splitflipkart = Table.SelectRows(flipkart, each ([Account] = "flipchart"))
splitothers = Table.SelectRows(flipkart, each not ([Account] = "flipchart"))
transform= Table.TransformColumns(splitflipkart, ....)
merge = Table.Combine(transform, splitaothers)
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
Proud to be a Datanaut!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.