Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I am looking to expand a ListColumn within a nested table, however I would like to so this without adding a new column. Is there a way to expand the list without adding a new column to the table or the nested table?
Here is an example close to how my data would look:
let
Source =
Table.FromRecords(
{
[Name = "Bob", Color = "Blue"],
[Name = "Jim", Color = "Yellow"],
[Name = "Paul", Color = "Green"]
}
)
in
Table.AddColumn (Source, "TypeID", each
Table.FromRecords(
{
[Type= "Small", ID = {1,2,3}],
[Type = "Medium", ID = {2,4,6}],
[Type = "Large", ID = {1,3,6}]
}
)
)
Solved! Go to Solution.
Hi,
So you probably want something like this:
let
Source =
Table.FromRecords(
{
[Name = "Bob", Color = "Blue"],
[Name = "Jim", Color = "Yellow"],
[Name = "Paul", Color = "Green"]
}
),
#"AddTab;e"=
Table.AddColumn (Source, "TypeID", each
Table.FromRecords(
{
[Type= "Small", ID = {1,2,3}],
[Type = "Medium", ID = {2,4,6}],
[Type = "Large", ID = {1,3,6}]
}
)
),
TransformColumn = Table.TransformColumns(#"AddTab;e",{"TypeID", each Table.ExpandListColumn(_, "ID")})
in
TransformColumn
Hope it helps,
Artur
Hi,
So you probably want something like this:
let
Source =
Table.FromRecords(
{
[Name = "Bob", Color = "Blue"],
[Name = "Jim", Color = "Yellow"],
[Name = "Paul", Color = "Green"]
}
),
#"AddTab;e"=
Table.AddColumn (Source, "TypeID", each
Table.FromRecords(
{
[Type= "Small", ID = {1,2,3}],
[Type = "Medium", ID = {2,4,6}],
[Type = "Large", ID = {1,3,6}]
}
)
),
TransformColumn = Table.TransformColumns(#"AddTab;e",{"TypeID", each Table.ExpandListColumn(_, "ID")})
in
TransformColumn
Hope it helps,
Artur
Hi @artpil,
This is great and worked a treat. I also had just figured out my own solution - so will copy below, in case there is a use case for either.
Thanks!
let
Source =
Table.FromRecords(
{
[Name = "Bob", Color = "Blue"],
[Name = "Jim", Color = "Yellow"],
[Name = "Paul", Color = "Green"]
}
)
in
Table.AddColumn (Source, "TypeID",
each
let
_usertable =
Table.FromRecords(
{
[Type= "Small", ID = {1,2,3}],
[Type = "Medium", ID = {2,4,6}],
[Type = "Large", ID = {1,3,6}]
}
)
in
Table.ExpandListColumn(
_usertable,"ID"
)
)
hello
if I correctly understood you below result which you want
let
Query1 = let
Source =
Table.FromRecords(
{
[Name = "Bob", Color = "Blue"],
[Name = "Jim", Color = "Yellow"],
[Name = "Paul", Color = "Green"]
}
)
in
Table.AddColumn (Source, "TypeID", each
Table.FromRecords(
{
[Type= "Small", ID = {1,2,3}],
[Type = "Medium", ID = {2,4,6}],
[Type = "Large", ID = {1,3,6}]
}
)
),
#"Expanded TypeID" = Table.ExpandListColumn(Table.ExpandTableColumn(Query1, "TypeID", {"ID"}, {"ID"}), "ID")
in
#"Expanded TypeID"
Hi @SolomonovAnton,
Almost - I want this result without having expanded the table TypeID table. So it should look like this:
The query table:
The TypeID table for record [Bob]:
Thanks!
Hi James,
Did you get the result you wanted? I'm trying to find out how to do the exact same type of issue
The query table:
The TypeID table for record [Bob]:
Hi @Anonymous ,
I did get this to work - if you look at my last reply above - I copied in the the solution to my reply to @artpil
Hi James,
Did you get the result you wanted? I'm trying to find out how to do the exact same type of issue
The query table:
The TypeID table for record [Bob]: