Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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]
Table = CROSSJOIN( PostCode, Road )
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] ) )
NameOfMin = LOOKUPVALUE ( 'Table'[NAME2], 'Table'[Result], MIN ( 'Table'[Result] ) )
Best Regards,
Herbert
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
We can already get both names, what is the final result you really want? Could you please describe it more exactly?
Best Regards,
Herbert
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |