optimize
1 Topic
Help with Power BI query optimization
Hi All, I need your help to optimize this power BI query. Currently, the query works very slow (query doesnt return result even after 3 days). let Source = Sql.Database("chvpkw8ahsv117", "hmparsdmd"), Staging_Transactions_Summary = Source{[Schema="Staging",Item="Transactions_Summary"]}[Data], #"Removed Other Columns" = Table.SelectColumns(Staging_Transactions_Summary,{"TransactionDate", "TransactionYYMM", "LocationID", "CardTypeDesc", "FuelTypeDesc", "td_acct_no", "PumpNumber", "IndoorOutDoor", "NFCUsed", "PilotSites", "TransactionCount", "Gallons", "FuelDollars"}), #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([IndoorOutDoor] = "Outdoor") and ([PilotSites] = "Yes") and ([NFCUsed] = "No")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"LocationID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"LocationID", Order.Ascending},{"td_acct_no", Order.Ascending},{"TransactionDate", Order.Ascending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"LocationID", "td_acct_no", "TransactionDate", "CardTypeDesc", "FuelTypeDesc", "PumpNumber", "IndoorOutDoor", "NFCUsed", "PilotSites", "TransactionCount", "Gallons", "FuelDollars"}), #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"LocationID", "td_acct_no","Index"},#"Staging Transactions_Summary",{"LocationID", "td_acct_no","Index"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"TransactionDate"}, {"NewColumn.TransactionDate"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.TransactionDate", "NextTransactionDate"}}), #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([NextTransactionDate] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "DaysFromLastTrans", each if [NextTransactionDate] <> null then Duration.Days(Duration.From([NextTransactionDate]-[TransactionDate])) else null), #"Grouped Rows" = http://itopssqldb.database.windows.net(#"Added Custom", {"LocationID", "TransactionYYMM", "td_acct_no", "CardTypeDesc", "FuelTypeDesc", "IndoorOutDoor", "NFCUsed"}, {{"TransactionCount", each Table.RowCount(_), type number}, {"TotalGallons", each List.Sum([Gallons]), type number}, {"TotalFuelDollars", each List.Sum([FuelDollars]), type number}, {"TotalDays", each List.Sum([DaysFromLastTrans]), type number}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "AvgDaysBetweenTrans", each [TotalDays]/[TransactionCount]) in #"Added Custom1"Solved7.9KViews0likes4Comments