Power Query calc across two tables

[dpotta]

Hi

I was looking for some help with a Query.  For each single row of the PostCode Table I would like to perform a calculation against every row of the Road Table and return the min value from the results list.

The calculation for each row is ((PostCode[GEOMETRY_X]-Road[GEOMETRY_X])*(PostCode[GEOMETRY_X]-Road[GEOMETRY_X])) - ((PostCode[GEOMETRY_Y]-Road[GEOMETRY_X])*(PostCode[GEOMETRY_Y]-Road[GEOMETRY_X]))

So the for each of the 370 lines in the PC table the calculation would be performed 245 times. I would like the minimum value to be returned along with the string from the NAME1 column of the Road table.

I have attached an example workbook here

Any help would be much appreciated.

Many thanks

Dave

[v-haibl-msft]

@dpotta

We can first cross join these two tables and then do the calculation to find out the minimum value. Both Power Query and DAX are listed as below.

[Use Power Query]

Road Table:

let
    Source = Excel.Workbook(File.Contents(".xlsx file path"), null, true),
    Road_Table = Source{[Item="Road",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Road_Table,{{"NAME1", type text}, {"GEOMETRY_X", type number}, {"GEOMETRY_Y", type number}, {"POSTCODE_DISTRICT", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1)
in
#"Added Custom"

PostCode Table:

let
    Source = Excel.Workbook(File.Contents(".xlsx file path"), null, true),
    PostCode_Table = Source{[Item="PostCode",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PostCode_Table,{{"NAME1", type text}, {"GEOMETRY_X", Int64.Type}, {"GEOMETRY_Y", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Custom"},Road,{"Custom"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"NAME1", "GEOMETRY_X", "GEOMETRY_Y"}, {"NewColumn.NAME1", "NewColumn.GEOMETRY_X", "NewColumn.GEOMETRY_Y"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Custom.1", each ([GEOMETRY_X] - [NewColumn.GEOMETRY_X]) * ([GEOMETRY_X] - [NewColumn.GEOMETRY_X]) - ([GEOMETRY_Y] - [NewColumn.GEOMETRY_X]) * ([GEOMETRY_Y] - [NewColumn.GEOMETRY_X])),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Custom.1", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
#"Kept First Rows"

[Use DAX]

1. In Road table, rename the columns of NAME1, GEOMETRY_X and GEOMETRY_Y to NAME2, GEOMETRY_X2 and GEOMETRY_Y2.
   
2. Create a new table which cross join the original two tables.
   

   Table = CROSSJOIN( PostCode, Road )

3. Create a column to store the calculate results.
   

   Result =
    ( ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] )
       * ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] ) )
       - ( ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_X2] )
   * ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_X2] ) )

4. Create a measure to return the string from the NAME2 column.
   

   NameOfMin = LOOKUPVALUE ( 'Table'[NAME2], 'Table'[Result], MIN ( 'Table'[Result] ) )

   

Best Regards,

Herbert

[dpotta]

@v-haibl-msft

Herbert

Many thanks for this!

So, I explained two things badly which I have corrected! Apologies

Firstly the calculation is ((PostCode[GEOMETRY_X]-Road[GEOMETRY_X])*(PostCode[GEOMETRY_X]-Road[GEOMETRY_X])) - ((PostCode[GEOMETRY_Y]-Road[GEOMETRY_Y)*(PostCode[GEOMETRY_Y]-Road[GEOMETRY_Y]))

Secondly, I wanted to convert negative numbers to positive from the results column and take the minimum number.

So what I end up with is:

Road M

let
    Source = Excel.Workbook(File.Contents("....xlsx"), null, true),
    Road_Table = Source{[Item="Road",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Road_Table,{{"NAME1", type text}, {"GEOMETRY_X", type number}, {"GEOMETRY_Y", type number}, {"POSTCODE_DISTRICT", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Custom.2"}, {"GEOMETRY_X", "GEOMETRY_X2"}, {"GEOMETRY_Y", "GEOMETRY_Y2"}, {"NAME1", "NAME2"}})
in
#"Renamed Columns"

PostCode M

let
    Source = Excel.Workbook(File.Contents("....xlsx"), null, true),
    PostCode_Table = Source{[Item="PostCode",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PostCode_Table,{{"NAME1", type text}, {"GEOMETRY_X", Int64.Type}, {"GEOMETRY_Y", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Number"},Road,{"Custom.2"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"NAME1", "GEOMETRY_X", "GEOMETRY_Y"}, {"NewColumn.NAME1", "NewColumn.GEOMETRY_X", "NewColumn.GEOMETRY_Y"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Custom.1", each 




([GEOMETRY_X] - [NewColumn.GEOMETRY_X]) * ([GEOMETRY_X] - [NewColumn.GEOMETRY_X]) - ([GEOMETRY_Y] - [NewColumn.GEOMETRY_Y]) * ([GEOMETRY_Y] - [NewColumn.GEOMETRY_Y])),
    #"Kept First Rows" = Table.FirstN(#"Added Custom1",1),
    #"Sorted Rows" = Table.Sort(#"Kept First Rows",{{"Custom.1", Order.Ascending}})
in
    #"Sorted Rows"

DAX:

1. In Road table, rename the columns of CUSTOM, NAME1, GEOMETRY_X and GEOMETRY_Y to , NUMBER, NAME2, GEOMETRY_X2 and GEOMETRY_Y2.

2. Create a column to store the calculate results.

Result = if(
 ( ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] )
    * ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] ) )
    - ( ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] )
* ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] ) )
<0,
( ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] )
    * ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] ) )
    - ( ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] )
* ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] ) )*-1,
 ( ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] )
    * ( 'Table'[GEOMETRY_X] - 'Table'[GEOMETRY_X2] ) )
    - ( ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] )
* ( 'Table'[GEOMETRY_Y] - 'Table'[GEOMETRY_Y2] ) )
)

3. Create a measure to return the string from the NAME2 column.

NameOfMin = LOOKUPVALUE ( 'Table'[NAME2], 'Table'[Result], MIN ( 'Table'[Result] ) )

And then the result is:

Which is the correct result - Brilliant!

What I would really like to do though is retreive a Road name, the NameOfMin for all (in this case) 370 PostCodes. 

Does anyone have any advice on this? Much appreciated if so. 

Many thanks

Dave

[v-haibl-msft]

@dpotta

We can already get both names, what is the final result you really want? Could you please describe it more exactly?

Best Regards,

Herbert

[dpotta · Accepted solution]

@Herbert_Liu

Hi Herbert

I worked out a different way to do what I needed to do. 

The Power Queries are for Road:

let
    Source = Excel.CurrentWorkbook(){[Name="Road"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME1", type text}, {"GEOMETRY_X", type number}, {"GEOMETRY_Y", type number}, {"POSTCODE_DISTRICT", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"NAME1", "NAME2"}, {"GEOMETRY_X", "GEOMETRY_X2"}, {"GEOMETRY_Y", "GEOMETRY_Y2"}})
in
#"Renamed Columns"

And then for Postcode

let
    Source = Excel.CurrentWorkbook(){[Name="PostCode"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Custom"},Road,{"Custom"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"NAME2", "GEOMETRY_X2", "GEOMETRY_Y2", "POSTCODE_DISTRICT"}, {"NAME2", "GEOMETRY_X2", "GEOMETRY_Y2", "POSTCODE_DISTRICT"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Distance", 

each if

(([GEOMETRY_X] - [GEOMETRY_X2]) * ([GEOMETRY_X] - [GEOMETRY_X2]))
 -
(([GEOMETRY_Y] - [GEOMETRY_Y2]) * ([GEOMETRY_Y] - [GEOMETRY_Y2]))

< 0 

then

(([GEOMETRY_X] - [GEOMETRY_X2]) * ([GEOMETRY_X] - [GEOMETRY_X2]))
 -
(([GEOMETRY_Y] - [GEOMETRY_Y2]) * ([GEOMETRY_Y] - [GEOMETRY_Y2]))

* -1

else

(([GEOMETRY_X] - [GEOMETRY_X2]) * ([GEOMETRY_X] - [GEOMETRY_X2]))
 -
(([GEOMETRY_Y] - [GEOMETRY_Y2]) * ([GEOMETRY_Y] - [GEOMETRY_Y2]))),


    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Distance", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Distance", Order.Ascending}})
in
    #"Sorted Rows"

And then in DAX to just pick the Min value for each Postcode as a match.

Many thanks for your help though : )

Dave