create table dynamically

[zimme063]

I am using Power BI desktop and I need to deliver a tool for users to easily view their data.  I am intersted in creating a new table based on a filtered main table where the filter parameters is stored in a list.  The end goal is to have the main table parsed into subtables so the users can find data easilty by the Table->Field hierarchy in the Power BI field bar.

A simplified example  -

Input

Table_Attributes:  Attributes of fruit (each fruit has a different attributes in the table);

List_Fruit: [Apples, Bananas, Pineapples]; 

Output

Table_Attributes

List_Fruit

Table_Apples

Table_Bananas

Table_Pineapples

[Eric_Zhang]

---

@zimme063 wrote:

I am using Power BI desktop and I need to deliver a tool for users to easily view their data.  I am intersted in creating a new table based on a filtered main table where the filter parameters is stored in a list.  The end goal is to have the main table parsed into subtables so the users can find data easilty by the Table->Field hierarchy in the Power BI field bar.

A simplified example  -

Input

Table_Attributes:  Attributes of fruit (each fruit has a different attributes in the table);

List_Fruit: [Apples, Bananas, Pineapples]; 

Output

Table_Attributes

List_Fruit

Table_Apples

Table_Bananas

Table_Pineapples

---

@zimme063

You can create the tables manually.

let
    Source = Table.SelectRows(Main_Table, each ([fruit] = "Apples")),
    tableApples = Table.SelectColumns(Source, Table.ToList(Table.SelectColumns(Table.SelectRows(Table_Attributes, each ([fruit] = "Apples")),"attributes")))

in
    tableApples

let
    Source = Table.SelectRows(Main_Table, each ([fruit] = "Bananas")),
    tableBananas = Table.SelectColumns(Source, Table.ToList(Table.SelectColumns(Table.SelectRows(Table_Attributes, each ([fruit] = "Bananas")),"attributes")))

in
    tableBananas

let
    Source = Table.SelectRows(Main_Table, each ([fruit] = "Pineapples")),
    tablePineapples = Table.SelectColumns(Source, Table.ToList(Table.SelectColumns(Table.SelectRows(Table_Attributes, each ([fruit] = "Pineapples")),"attributes")))

in
    tablePineapples

However I don't think you can create the tables dynamically according to the List_Fruit table. For me it doesn't make much sense, for example, provided the dynamically way would work, when the list_fruit table changes, after refreshing, there're more/less tables,  for newly added tables, they were not used in the  report visuals. For the missing tables, you reports would show errors. In my opinion, dynamical schema and metadata is not a good practice.

I guess we can get more proper suggestion in Power Query from @ImkeF. 🙂