-1

I need to union multiple datasets with different schema into one dataset with all columns. For example: Table 1 has columns: table_name, 1A and 1B and Table 2 has columns: table_name, 2A and 2B. So the final table would have columns: table_name, 1A, 1B, 2A and 2B.like below. I need to represent the final dataset using Table visual in PowerBI and my aim is to use a table slicer to filter out unrelated columns. For example, if I select Table 1 in slicer, the table view should only display columns 1A and 1B and filter out columns 2A and 2B since they have no relevance to Table 1. Likewise, when selecting Table 2 from slicer, it should only show columns 2A and 2B. I thought this can be easily achieved in PowerBI but i was wrong. Anyway who has worked out a solution please could you let me know? Many Thanks!

Table1Table1 Table2Table2 FinalTableFinalTable

Mich
  • 19
  • 1
  • 1
    A huge misconception about how data analysis and Power BI works. Maybe this can help: [Getting started with Power BI](https://learn.microsoft.com/en-us/power-bi/fundamentals/desktop-getting-started) – Peter Feb 02 '23 at 07:08

1 Answers1

0

The suggestion is to use the column name same for both tables. Then you will have 3 columns in your final table like- table_name, column_a and column_b. rest is as simple as you planned.

I did some play with your requirement as I know the end user can ask for anything whether it makes sense or not. I have created a table considering your condition as below-

enter image description here

The next step is I have applied some transformation to the data, so that I can achieve the requirement in the visual layer.Here below is the output I will use in the visual. I have added an extra column Index which will be used in the visual for some purpose-

enter image description here

Now, in the visual layer, I have used a Matrix (only option) to implement the option of adding and removing columns dynamically. You have to bear an extra column "Index" in the visual for no reason, but you can take it as row_number :)

enter image description here

Now, if you select a table name from the slicer, you will only see values/rows from that specific table as below-

enter image description here

You can check the Power BI file from here.

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • Hi there, thanks for looking into this. However, I am stacking multiple tables with different columns on top of each other. These are different data fields. I have 20 tables unioned into one with 150ish columns altogether, and some columns are going to be entirely null. If it were up to me, I wouldn't want to do it this way, but have no other option. – Mich Feb 02 '23 at 10:05
  • You can check some tutorial like - https://www.youtube.com/watch?v=Bt_m5eVlUhA – mkRabbani Feb 02 '23 at 10:48
  • You can check this tricks as well - https://stackoverflow.com/questions/57924019/show-hide-column-in-power-bi-table-matrix-visual – mkRabbani Feb 02 '23 at 16:18
  • Hi @Mich, I have added an option in the answer. You can check. – mkRabbani Feb 03 '23 at 04:51
  • 1
    Hi, Thanks very much for this! I almost gave up and gone back to the backend and created two more tables as referencing and bridging tables so I could set up relationship in PowerBI Model pane and work from there. Your solution looks very neat and clean, will definitely have a play around! Thanks again!! – Mich Feb 09 '23 at 09:15