lookup table...but how?

[adavid999]

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:

T1T2

"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:

Result

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

[Anonymous · Accepted solution]

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.

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

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

First step the data of atble T2.

then merge T2 with T1

expanding ...

and finally ... averaging

[Icey · Accepted solution]

Hi @adavid999 ,

The workaround @Anonymous  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] )

Best Regards,

Icey

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

[Anonymous]

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"

[adavid999]

Thanks @Anonymous 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!

[Anonymous · Accepted solution]

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.

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

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

First step the data of atble T2.

then merge T2 with T1

expanding ...

and finally ... averaging

[Greg_Deckler]

@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.