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

lookup table...but how?

Hello,

I am working in Excel/Power Query struggling to come up with the correct way to establish relationship between tables to allow a difference calculation.

Table 1 and 2 in power query are something like this:

T1T1T2T2

"Region" occurs multiple times in both tables but "Town" only occurs in T1 (but multiple times). One Region might link to a number of different Towns

 

I have a "Question" lookup table which I use for my pivot table output and I can calculate the respective average scores for Town and Region but I am not sure how to join these two tables to allow an aggregated output pivot table as below:

 

ResultResult

So in T3, Town A would be related to Region A1 and Town B is related to Region B1. Copying T1 and removing duplicates of Town to create a lookip doesn't work because multiple instances of Region can apply to different Towns. Ie. Town A could be A1, but Town C (not shown) could also be A1.

 

If it makes sense, the only way I could see a link working is that if T2 was aggregated first, so there is only one row for each Region (a Region average) but I am not sure how to do this, i.e. how can I acheive this with a measure that I can drop into result pivot table that will give me Region average alongside Town average. I hope this makes sense, please let me know if it doesn't!

 

Many thanks,

 

A

2 ACCEPTED SOLUTIONS

thanks for the trust 🙂.
I try to explain better, but I don't know what is not clear to you and what you want to modify.

image.png

 

T1 is the name of query that contains the table T1.

 

T2 is the name of the query which make the job:

 

image.png

First step the data of atble T2.

 

then merge T2 with T1

 

image.png

 

expanding ...

 

 

image.png

 

 

 

and finally ... averaging

 

image.png

 

View solution in original post

Icey
Community Support
Community Support

Hi @adavid999 ,

 

The workaround @Rocco_sprmnt21  provided is great.

And you can also try to use DAX to create a calculated table.

Table =
VAR t =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Table1,
                "No1", [Q no.],
                "Region1", [Region],
                "Town", [Town],
                "Town Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table1[Town Score] ), ALLEXCEPT ( Table1, Table1[Town] ) ),
                    CALCULATE ( COUNT ( Table1[Town] ), ALLEXCEPT ( Table1, Table1[Town] ) )
                )
            ),
            SELECTCOLUMNS (
                Table2,
                "No2", [Q no.],
                "Region2", [Region],
                "Region Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table2[Region Score] ), ALLEXCEPT ( Table2, Table2[Region] ) ),
                    CALCULATE ( COUNT ( Table2[Region] ), ALLEXCEPT ( Table2, Table2[Region] ) )
                )
            )
        ),
        [No1] = [No2]
            && [Region1] = [Region2]
    )
RETURN
    SUMMARIZE ( t, [No1], [Town], [Town Score Avg], [Region Score Avg] )

dax.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hi @adavid999 ,

 

The workaround @Rocco_sprmnt21  provided is great.

And you can also try to use DAX to create a calculated table.

Table =
VAR t =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Table1,
                "No1", [Q no.],
                "Region1", [Region],
                "Town", [Town],
                "Town Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table1[Town Score] ), ALLEXCEPT ( Table1, Table1[Town] ) ),
                    CALCULATE ( COUNT ( Table1[Town] ), ALLEXCEPT ( Table1, Table1[Town] ) )
                )
            ),
            SELECTCOLUMNS (
                Table2,
                "No2", [Q no.],
                "Region2", [Region],
                "Region Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table2[Region Score] ), ALLEXCEPT ( Table2, Table2[Region] ) ),
                    CALCULATE ( COUNT ( Table2[Region] ), ALLEXCEPT ( Table2, Table2[Region] ) )
                )
            )
        ),
        [No1] = [No2]
            && [Region1] = [Region2]
    )
RETURN
    SUMMARIZE ( t, [No1], [Town], [Town Score Avg], [Region Score Avg] )

dax.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

 

 

try this

T2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYRDDSClWJxpI6iglgTBIyBgshKLKGFOViVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Q = _t, T = _t, R = _t, Ts = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q", Int64.Type}, {"T", type text}, {"R", type text}, {"Ts", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"R"}, T1, {"R"}, "T2", JoinKind.LeftOuter),
    #"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"Rs"}, {"Rs"}),
    #"Grouped Rows" = Table.Group(#"Expanded T2", {"Q","T"}, {{"avg Ts", each List.Average([Ts]), type number}, {"avg Rs", each List.Average([Rs]), type number}})
in
    #"Grouped Rows"

T1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEESZKsTrRSkZAVhKIawrmwmXNUGXNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Q = _t, R = _t, Rs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q", Int64.Type}, {"R", type text}, {"Rs", Int64.Type}})
in
    #"Changed Type"

 

Thanks @Rocco_sprmnt21 I am sure this would solve it but I wouldn't know where to start adapting this to work on my tables - it looks too complex!

thanks for the trust 🙂.
I try to explain better, but I don't know what is not clear to you and what you want to modify.

image.png

 

T1 is the name of query that contains the table T1.

 

T2 is the name of the query which make the job:

 

image.png

First step the data of atble T2.

 

then merge T2 with T1

 

image.png

 

expanding ...

 

 

image.png

 

 

 

and finally ... averaging

 

image.png

 

View solution in original post

Greg_Deckler
Super User IV
Super User IV

@adavid999 - So it seems like you should have a Town dimension, Region dimension and Question dimension. If you had that and related them to your 2 fact tables, then you should be able to aggregate things fairly easily within a single visual I would think.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors