cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
21010-10-2020
21112-10-2020

 

Table 3: CP

Fil #CP
21020-10-2020
21123-10-2020

 

I want to show this in one table as, so I can show one table with a filter on Win/CP:

Fil #FilDateWin/Cp
210Kapelle10-10-2020Win
210Kapelle20-10-2020CP
211Berk12-10-2020Win
211Berk23-10-2020CP

 

How can I combine these?

1 ACCEPTED SOLUTION
Super User III
Super User III

@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 

SU18_powerbi_badge

   

View solution in original post

3 REPLIES 3
Super User III
Super User III

@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 

SU18_powerbi_badge

   

View solution in original post

Helper I
Helper I

Thanks @AlB!

 

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

Itzarthi_0-1605015693198.png

And it gives an error at the end:

Itzarthi_1-1605015913018.png

 

Super User III
Super User III

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 

SU18_powerbi_badge

 

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors