Return columns from multiple tables

[Anonymous]

Hi,

I have the following scenario -

1 master summary table with multiple rows, each row includes a type.

Multiple detail tables (1 per type), some containing a sub type, and some not.  1 type doesn't have a matching detail table.  The rows in the detail tables use the same unique identifier as the corresponding row in the summary table.

Example

Summary table - 

Identifier     Desc                      Type 

1                 Row 1                    A

2                 Row 2                    A

3                 Row 3                    B

4                 Row 4                    C

5                 Row 5                    D

6                 Row 6                    D

7                 Row 7                    E

Detail table Type A -

Identifier    Sub Type

1                 A-1

2                 A-2

Detail table Type B - 

Identifier    Sub Type

3                 B-1

Detail table Type C -

Identifier

4                  No sub type

Detail table Type D -

Identifier    Sub Type

5                 D-1

6                 D-2

there is no detail table for type E.

What I am trying to do is build a table that can be filtered on the sub type (amongst other filters) where appropriate - so:

Slicer selects sub type A-1 - list displays row 1 only.

Identifier     Desc                      Type     Sub Type

1                 Row 1                    A           A-1

Slicer selects sub type D-1 - list displays rows 5 & 6 only.

Identifier     Desc                      Type     Sub Type

5                 Row 5                    D           D-1

6                 Row 6                    D           D-1

Slicer selects blank sub type - list displays rows 4 & 7 only

Identifier     Desc                      Type     Sub Type

4                 Row 4                    C

7                 Row 7                    E

Slicer has no select - list displays all rows. 

Identifier     Desc                      Type     Sub Type

1                 Row 1                    A           A-1

2                 Row 2                    A           A-2

3                 Row 3                    B           B-1

4                 Row 4                    C

5                 Row 5                    D           D-1

6                 Row 6                    D           D-1

7                 Row 7                    E

I'm new to Power BI, but wouldn't have any problem with SQL or SSRS.  I'm restricted to using a supplier developed model - I can create new measures, but not new columns.

[v-cherch-msft]

Hi @Anonymous 

You may use 'append queries' for TableA...TableD in query editor first.Then link the append table with the summary table.Attached sample file for your reference.

Regards,

[Anonymous]

Unfortunately I don't have access to the query editor for this application.  The supplier has provided a data model with no ability for me to change the underlying queries.

[Washivale]

Hello @Anonymous ,

one way to achieve this is consolidate tables A to D using Union, Then using Manage relationships define relationship between summary table and Consolidated table on Identifier.

then create visual with fields "Identifier , Desc, Type " from Summary table and "Sub Type" from Consolidated Table

Use Subtype from consolidated table as your table Slicer.

let me know if it helps

[Anonymous]

Hi,  thanks - but is this in he query editor?  I have no access to the query editor as the supplier has provided a data model that I have no access to change.  As previously mentioned, I can create measures but not calculated columns.

[Anonymous]

Hello, 

I think you have the solution in this post:

https://community.powerbi.com/t5/Desktop/Create-a-new-table-from-columns-from-existing-tables/td-p/9...

Best Regards,

JO

[v-cherch-msft]

Hi @Anonymous 

You may use below formula to create the table:

Table = UNION(TableA,TableB,TableD)

Regards,

[Anonymous]

unfortunately the detail tables are not the same layout - the different types have different numbers of columns, and not all of them have a sub-type.  Also I have no access to the query editor.

[v-cherch-msft]

Hi @Anonymous 

You may follow the workaround in the post which mentioned by jooliveira to create the table:New Table= UNION(SELECTCOLUMNS...)

https://community.powerbi.com/t5/Desktop/Create-a-new-table-from-columns-from-existing-tables/td-p/98338

Regards.

[Anonymous · Accepted solution]

Thanks for the suggestions - looking at them lead me down another path and I found the best solution (for me at least) is to create a series of measures to extract the sub-type for each row -

Sub Type MST1 =

CALCULATE (

    VALUES ('REC_TYPE1'[ST1] ) ,

    FILTER (

        'MASTER TABLE' ,

        'MASTER TABLE'[Rec ID] = SELECTEDVALUE ( 'MASTER TABLE'[Rec ID] )

    )

)

 then MST2, MST3, MST4.

And then use another measure to concatenate the individual type measures -

Sub-Type =

CONCATENATE (

    'MASTER TABLE'[MST1] ,

    CONCATENATE (

        'MASTER TABLE'[MST2] ,

        CONCATENATE (

            'MASTER TABLE'[MST3] ,

           'MASTER TABLE'[MST4]

        )

    )

)

[v-cherch-msft]

Hi @Anonymous 

Glad to hear the reply is helpful, please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,