Forum Discussion

adavid999's avatar
adavid999
Helper V
6 years ago
Solved

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 some...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Icey
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

 

 

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's avatar
    adavid999
    Helper V
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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