Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JHopkins
New Member

Combining two pivoted tables ("pivoted" by groupby in tables)

Hello,

I have been beating my head agains a wall for a week and need the help others.  I am able to pivot two sets of data into two separate report screens.  I am also able to "pivot" the two sets of data with GROUPBY into two separate tables.  I cannot for the life of me figure out how to combine the two tables into one.

Made Table:

DimensionDateMade
12020-11-031000
22020-11-031100
32020-11-03900
42020-11-031200
52020-11-031300
62020-11-03100
72020-11-03500
82020-11-03600
92020-11-03500

 

Sampled Table:

DimensionDateSampled
12020-11-0350
22020-11-0330
32020-11-0350
42020-11-0350
52020-11-0330
62020-11-0330
72020-11-0310
82020-11-0350
92020-11-0320

 

I've tried joins,different summarizes, and have gotten very lost trying to understand the more detailed use of filters etc....My goal is to be able to display all of the data in one table like this:

DimensionDateMadeSampled
12020-11-03100050
22020-11-03110030
32020-11-0390050
42020-11-03120050
52020-11-03130030
62020-11-0310030
72020-11-0350010
82020-11-0360050
92020-11-0350020

There are probably better ways when I bring the data in or when I create the tables themselves...I am hoping some more experienced minds can enlighten me.

Thanks!

Jeremy

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @JHopkins ,

 

There are two ways, one is to merge them in Power Query Editor.

 

comb1.jpg

 

comb2.jpg

 

Another way is to create a calculate column in Made table.

 

Sample column = 
CALCULATE(
    SUM('Sampled Table'[Sampled]),
    FILTER(
        'Sampled Table',
        'Sampled Table'[Dimension]='Made Table'[Dimension]
        &&'Sampled Table'[Date]='Made Table'[Date]))

 

comb3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

2 REPLIES 2
v-zhenbw-msft
Community Support
Community Support

Hi @JHopkins ,

 

There are two ways, one is to merge them in Power Query Editor.

 

comb1.jpg

 

comb2.jpg

 

Another way is to create a calculate column in Made table.

 

Sample column = 
CALCULATE(
    SUM('Sampled Table'[Sampled]),
    FILTER(
        'Sampled Table',
        'Sampled Table'[Dimension]='Made Table'[Dimension]
        &&'Sampled Table'[Date]='Made Table'[Date]))

 

comb3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

lbendlin
Super User
Super User

This should work:

 

let
    Source = Table.NestedJoin(#"Made Table", {"Dimension"}, #"Sampled Table", {"Dimension"}, "Sampled Table", JoinKind.Inner),
    #"Expanded Sampled Table" = Table.ExpandTableColumn(Source, "Sampled Table", {"Sampled"}, {"Sampled"})
in
    #"Expanded Sampled Table"

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors