IF statement in Power Query doubling the refresh time

[masplin]

I have written a PQ where 2 tables are merged into the main table in order to filter the main table by date and location.

The table I actual load to the data model has this query

let    Source = Reporting_Plus_Fact,
    #"Removed Columns" = Table.RemoveColumns(Source,{"StockQty", "Description", "Weight", "ServiceChargeValue", "TaxAddOnValue", "ModifierNo", "PriceLevel", "IsCondiment", "CompValue", "CompReason", "Reporting_Clerks"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"PluNo", "PLU"}, {"TotalValue", "Line Retail Sale"}, {"AdjustmentsValue", "Line Retail Discount"}, {"TaxNonAddValue", "VATraw"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([CorrectionType] = 0)),
    #"Merged BusDate" = Table.NestedJoin(#"Filtered Rows",{"BusDateID"},Reporting_BusDates,{"BusDateID"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged BusDate", "NewColumn", {"BusDate"}, {"BusDate"}),
    #"Merged Location" = Table.NestedJoin(#"Expanded NewColumn",{"TransID"},Reporting_Transactions_Fact,{"TransID"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Location", "NewColumn", {"Location"}, {"Location"}),
    #"Merged Time" = Table.NestedJoin(#"Expanded NewColumn1",{"TimeID"},Reporting_Times,{"TimeID"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Time", "NewColumn", {"Hour", "Minute"}, {"Hour", "Minute"}),
    #"Renamed Date" = Table.RenameColumns(#"Expanded NewColumn2",{{"BusDate", "Transaction Date"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Date",{"BusDateID", "TimeID", "CorrectionType"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"VATraw", Currency.Type}}),
    #"Added VAT" = Table.AddColumn(#"Changed Type", "VAT", each Number.Round([VATraw],3,RoundingMode.AwayFromZero)),
    #"Removed VATraw" = Table.RemoveColumns(#"Added VAT",{"VATraw"})
in
    #"Removed VATraw"

I wanted to parameterise the BusDate and Location parts of this so the user had control over the data loaded. The Merge Location part comes form a table that has this code

let    // Lines from parameter table 


    Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],


    Location_Value = Parameter{0}[Value],


    Source = Sql.Databases("tcp:food.pod.uk,1433"),
    StockNetPOD_Journal = Source{[Name="StockNetPOD_Journal"]}[Data],
    dbo_Reporting_Transactions_Fact = StockNetPOD_Journal{[Schema="dbo",Item="Reporting_Transactions_Fact"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Reporting_Transactions_Fact, each true),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"TransID", "SalesMode", "SalesType", "Location"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and [Location] = Location_Value)


in
    #"Filtered Rows1"

This works absolutely fine and the speed of load is similar tor slightly faster than the original SQL query i had.

However this only allows the user to select 1 location. In order to allow them to select all location I added a drop down choice that put 100 into the Location_Value and changed the last line to read

(if Location_Value=100 then [Location]>0 else [Location] = Location_Value))

This works correctly, but the refresh takes double the time to refresh a single location. I'm new to PQ so can someone explain what I have done wrong as it isn't logical that this query takes longer?

Thanks for any advice

Mike

 

[ImkeF]

Isn't the amount of data to be pulled from the server significantly more if you don't choose just one location but all?

 

[masplin]

Well yes but I was comparing just choosing one location, so using the false part of the if. Should be exactly the same. I actually discovered later that the speed issue was caused by trying to change the whole thing into numbers. My data set in PQ intially returned 0010 as the location so I used a text entry in excel "0010" as the parameter value. Where it started to go wrong was I changed the entry in excel to a number i.e. 10, so I could have > in my if, and did a change type in PQ to "whole number". Even if I remove the if statement and just have [Location] = Location_Value this is 2.5 times slower. It did eventually calculate, but I couldn't understand why it was slower. I suspect it is something to do with the type in excel not matching exactly the type in PQ, so it has to do some translation to work out they are equal. Seems bizarre.

My thoughts are when you do a change type in PQ it is called Int64.Type. Maybe this is not the same as making an excel text into a number? Alternatively maybe when doing this test, PQ still thinks [Location] is text so I need to wrap it in Number.FromText to force it to be comparing a number on both sides? I had to change it all back to text to get some results, so I need to try these out when i have time. Love to know the issue to avoid the problem in future

 

[masplin]

For anyone who is interested there seems to be a fundamental problem trying to move from text to numbers

I ran 4 time trials for various ways of doing this mm:ss.

1. Text in PQ and text in excel 1:40
2. Change PQ colum to type "whole number" and in excel use the "convert to number" function. When I checked the format it says "General" 7:30!!!!
3. Format the number in excel to "number" 0 decimals 3:30
4. Change the PQ query to also use in the parameter section Location_Value = Int64.From(Parameter{0}[Value]) that should be forcing the value from Excel to be exactly the same format as PQ code below to change the column data

​

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),

However it is still only 3:10 so slightly faster than making sure the excel number is actually a number, but still 50% slower than using a text match.

This seems really really odd and poor behavior!!!! I guess I'm stuck with text unless anyone else has any bright ideas?

mike

 

[ImkeF]

Hi Mike,
not sure if I fully got whats going on here, but some rule-of-thumb tips on this:

- don't change the format of your SQL-Server data, this might prevent query folding. So if there is no way around it, do it as late as possible.
- if you need to change the format of Excel-sourced-data: Do it in PQ
- Play around with "Table.Buffer". Although this will prevent query folding, it sometimes boosts performance
- use SQL-Profiler to study how your M-code will be transated to the Server (which commands are actually done by the server and what not)

 

[masplin]

Maybe the first thing you said it what is happening. I changed the format in the table that then gets merged as the filter is being applied to that table pre-merge. Maybe that then messes up the folding. So possibly doing the merge first and then applying the filter right at the end would be better. Thanks for the tips again.