Split tables in different rows

[Itzarthi]

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?

[AlB · Accepted solution]

@Itzarthi 

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 

[Itzarthi]

Thanks @AlB!

It is not working correctly, at unpivoted it shows everything and not only CP/Win.

And it gives an error at the end:

[AlB]

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