Forum Discussion
Split tables in different rows
Hey Guys,
I have three different tables:
Table 1: Fil
| Fil # | Fil |
210 | Kapelle |
211 | Berk |
Table 2: Win
| Fil # | Win |
| 210 | 10-10-2020 |
| 211 | 12-10-2020 |
Table 3: CP
| Fil # | CP |
| 210 | 20-10-2020 |
| 211 | 23-10-2020 |
I want to show this in one table as, so I can show one table with a filter on Win/CP:
| Fil # | Fil | Date | Win/Cp |
| 210 | Kapelle | 10-10-2020 | Win |
| 210 | Kapelle | 20-10-2020 | CP |
| 211 | Berk | 12-10-2020 | Win |
| 211 | Berk | 23-10-2020 | CP |
How can I combine these?
It looks like I forgot to upload the file earlier. Check it out. It is working based on your sample tables. Have a look and see what you might be doing differently on the real data. Otherwise share a file with the real data (or a dummy that reproduces the issue).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
3 Replies
It looks like I forgot to upload the file earlier. Check it out. It is working based on your sample tables. Have a look and see what you might be doing differently on the real data. Otherwise share a file with the real data (or a dummy that reproduces the issue).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Itzarthi
You can do this best in PQ. Place the following M code in a blank query to see the steps. See it all at work in the attached file.
let Source = Table.NestedJoin(Fil, {"Fil #"}, CP, {"Fil #"}, "CP", JoinKind.LeftOuter), #"Merged Queries" = Table.NestedJoin(Source, {"Fil #"}, Win, {"Fil #"}, "Win", JoinKind.LeftOuter), #"Expanded CP" = Table.ExpandTableColumn(#"Merged Queries", "CP", {"CP"}, {"CP"}), #"Expanded Win" = Table.ExpandTableColumn(#"Expanded CP", "Win", {"Win"}, {"Win"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Win", {"Fil #", "Fil"}, "Win/CP", "Date"), #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Fil #", "Fil", "Date", "Win/CP"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Fil #", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
