Merge table

[mysho999]

Hi guys.

I need help with the final dashboard. I have one default table (connected to power BI via MySQL). From the main table, I need to visualize two main data in the dashboard (in the table) (for each data I want to visualize I made a separate table from the main table using unpivot-i don't know if this is the right procedure). Finally, I would need to merge (from the first table ,,5S otázka" + value and ,,Attribute" + value from the second table) the data into one table and visualize in the dashboard in one table (last picture,in the dashboard for example I visualized two tables but finally I would need to have it in one)

If possible, please advise on normal Power BI functionality without using DAX

Main table

Table1

Table 2

Final table

Thank you

[AllisonKennedy]

If you want to merge them, you could do that in the Power Query Editor so that they all sit in 1 flat table. I'm assuming you want the Note 1.1 next to Status 1.1 for Item 1? 

You could try this with Relationships by relating Table 1 and Table 2 each to the original column using that first column row number as Key, or you can do this in Power Query using merge: 

Since you have already done the separating and unpivoting, now you need to piece them back together. If you don't have that first column (doesn't have a name in your screenshot but I'll call it "Index") in Table1 and Table2, get it back in them. 

Then in the ADD COLUMN tab, extract the 1.2, 1.3, etc part from the Attribute columns in both tables Table1 and Table2. Call this new column AttNo

Finally, in the ADD COLUMN tab, merge the Index and AttNo columns in both Table 1 and Table 2. Call this merged column MergeKey

Now you're ready to merge the tables. In the Home tab, Merge Queries as New. Select the MergeKey from Table1 and Table2. Select the Join Kind (I think you might want Full Outer here). 

Finally, expand the Table2 column to select the Attribute and Value. If you used Full Outer, you'll want to select a lot of the other columns as well, and then will need to Merge them with the Table1 values. Easiest way to do this without custom M code is to create a new conditional column which says IF Table1[Name] is null then Table2[Name] else Table1[Name]. Then Remove the Table1 and Table 2 Names and keep only the newly merged Name. 

A bit hard to explain all that without images, so let me know if you have any questions. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[edhans]

@mysho999 - if @AllisonKennedy's good overview doesn't help, please post data that we can work with to assist.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[mysho999]

Hi,

thanks for the advice, but unfortunately it's still not clear to me. In the attachment https://drive.google.com/open?id=1uSFokNMxkiakgs-n7bHHkWe_D_5nmGF8  i send Excel which contains two cards. The first tab contains the source data and the second tab a preview of how I need to visualize the data in one table in power BI

https://drive.google.com/open?id=1uSFokNMxkiakgs-n7bHHkWe_D_5nmGF8 

Thank you 1000 times