Filter Table Visual to Only Show Column Names Listed on Another Table

[WALEED]

Gents,

Searched high and low without success, this seems like a problem someone must've stumbled upon by now but I can't find an answer!

I'll use the 2 tables below to demonstrate:

Data table (full):

Field A	Field B	Field C	Field D
DATA	DATA	DATA	DATA
DATA	DATA	DATA	DATA
DATA	DATA	DATA	DATA

Rules table:

Table Name	Fields
Table 1	Field A
Table 1	Field B
Table 1	Field D
Table 2	Field A
Table 2	Field B

I want the tables below!

Table 1

Field A	Field B	Field D
DATA	DATA	DATA
DATA	DATA	DATA
DATA	DATA	DATA

Table 2

Field A	Field B
DATA	DATA
DATA	DATA
DATA	DATA

Ideally, those should be visual tables where someone in the organization can access Power BI from the website and export Table 1 or Table 2 into .xlsx

I can manually drag the fields I need onto a table visual and export as intended on the website. But the number of different tables is big, and the list of rules are always changing! The slicer is handy when filtering rows but would be great if I could do the same for columns.

[WALEED · Accepted solution]

Many thanks to @v-shex-msft and @Zubair_Muhammad,

Finally found a solution that works for my problem:

Simply have to filter the rules table to show only the rules for a Table (e.g. Table 1)

RulesTableFiltered (filtered):

Table Name	Fields
Table 1	Field A
Table 1	Field B
Table 1	Field D

Then use this code:

Table1 = Table.SelectColumns(Source, RulesTableFiltered[Fields])

Result:

Source:

Field A	Field B	Field C	Field D
DATA	DATA	DATA	DATA
DATA	DATA	DATA	DATA
DATA	DATA	DATA	DATA

Table1:

Field A	Field B	Field D
DATA	DATA	DATA
DATA	DATA	DATA
DATA	DATA	DATA

It pulls the column names I want from the rules table. Whenever the rules for this table change, the sub table will automatically expand/shrink/change.

This sorts the problem on the data query side of things. Time to find a visual that displays a whole table as is. I'll keep reading for alternative solutions and mark a different answer as the solution if it's better suited or done at a visual level rather than query level.

[Zubair_Muhammad]

HI @WALEED

Hope this is what you need

From the Modelling Tab, add new tables with following formula

Table 1 =
ALL ( Data[Field A], Data[Field B], Data[Field D] )

Table 2 =
ALL ( Data[Field A], Data[Field B] )

[WALEED]

Hi @Zubair_Muhammad

Thank you for the quick response but I'm afraid this doesn't solve my problem.

I'm after something dynamic; let's say that (after a data refresh) the rules table changes the fields for Table 1 to (A,B,C) instead of (A,B,D). I'd like Table 1 to show those changes automatically.

This is a very basic example. What I have in reality is close to 250 fields and about 30 ever changing tables.

[v-shex-msft]

Hi @WALEED,

According to your description, you want to create a navigation table which used to store table name and their column names, right?

If this is a case, you can refer to below steps to achieve your requirement.

Sample database: sql server.

1. Get data from your database and choose one table to build the query.

let
    Source = Sql.Databases("xxxxxx"),
    xxxxx = Source{[Name="xxxxx"]}[Data],
    dbo_test01 = xxxxx{[Schema="dbo",Item="test01"]}[Data]
in
    dbo_test01

 

2. Duplicate query, remove and add some custom steps to get the table list of your database.

let
    Source = Sql.Databases("xxxxxx"),
    xxxxx = Source{[Name="xxxxx"]}[Data],
    #"Filtered Rows" = Table.SelectRows(xxxxx, each ([Kind] = "Table"))
in
    #"Filtered Rows"

3. Add custom column to get column name list from tables.

    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Fields", each Table.ColumnNames([Data]))

4. Remove other columns and expand the column name list.

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Fields"}),
    #"Expanded Fields" = Table.ExpandListColumn(#"Removed Other Columns", "Fields")

Full query:

let
    Source = Sql.Databases("xxxxxx"),
    xxxxx = Source{[Name="xxxxx"]}[Data],
    #"Filtered Rows" = Table.SelectRows(xxxxx, each ([Kind] = "Table")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Fields", each Table.ColumnNames([Data])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Fields"}),
    #"Expanded Fields" = Table.ExpandListColumn(#"Removed Other Columns", "Fields")
in
    #"Expanded Fields"

Regards,

Xiaoxin Sheng

[WALEED]

Hi @v-shex-msft,

Thank you for the answer, it's taught me a few things but it's still not the solution for my problem.

The navigation table already exists, as well as one big table with all the data for all the fields.

The navigation table decides how the fields from the source table will be divided into small groups of fields.

Table 1 and Table 2 do not exist, they're the ones I want to generate.