Power BI从文件夹导入超500万条数据刷新耗时过长的优化方案咨询
Hey there, I see you're stuck with a 15-minute refresh time for your Power BI report pulling from local .dif files—total pain, right? Let's break down your query and fix those bottlenecks. First, here's the query you shared for reference:
let Source = Folder.Files("Q:\Objekt\ABC\DEF\XYZ\1. Source Data"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".dif")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Folder Path"}), #"Inserted Text Between Delimiters" = Table.AddColumn(#"Removed Other Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Folder Path], "\", "\\", 6, 0), type text), #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "System"}}), BinaryToCSV = Table.AddColumn(#"Renamed Columns", "Custom", each Csv.Document(Binary.Buffer([Content]),[Delimiter="#(tab)", Columns=23, Encoding=1200, QuoteStyle=QuoteStyle.None])), #"Removed Columns" = Table.RemoveColumns(BinaryToCSV,{"Content"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"System"}, {{"Count", each _, type table [Folder Path=text, System=text, Custom=table]}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Custom"}, {"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded Count", each [System] = SystemToChoose), #"Expanded Custom" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "Date&Time"}, {"Column2", "P0 [mbar]"}, {"Column3", "P1 [mbar]"}, {"Column4", "P3 [mbar]"}, {"Column5", "P7 [mbar]"}, {"Column6", "PS160 [mbar]"}, {"Column7", "P190 [mbar]"}, {"Column8", "P220 [mbar]"}, {"Column9", "Q1 [ppb]"}, {"Column10", "Q2 [ppb]"}, {"Column11", "Q3 [ppb]"}, {"Column12", "Q4 [ppb]"}, {"Column13", "T11 [°C]"}, {"Column14", "T21 [°C]"}, {"Column15", "T31 [°C]"}, {"Column16", "T12 [°C]"}, {"Column17", "T22 [°C]"}, {"Column18", "T32 [°C]"}, {"Column19", "T01 [°C]"}, {"Column20", "T02 [°C]"}, {"Column21", "FM1 [slm]"}, {"Column22", "FM2 [slm]"}, {"Column23", "FM3 [slm]"}}), #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns1", each [#"P0 [mbar]"] <> "" and [#"P0 [mbar]"] <> "mbar" and [#"P0 [mbar]"] <> "P_0"), #"Cleaned Text" = Table.TransformColumns(#"Filtered Rows2",{{"System", Text.Clean, type text}, {"Date&Time", Text.Clean, type text}, {"P0 [mbar]", Text.Clean, type text}, {"P1 [mbar]", Text.Clean, type text}, {"P3 [mbar]", Text.Clean, type text}, {"P7 [mbar]", Text.Clean, type text}, {"PS160 [mbar]", Text.Clean, type text}, {"P190 [mbar]", Text.Clean, type text}, {"P220 [mbar]", Text.Clean, type text}, {"Q1 [ppb]", Text.Clean, type text}, {"Q2 [ppb]", Text.Clean, type text}, {"Q3 [ppb]", Text.Clean, type text}, {"Q4 [ppb]", Text.Clean, type text}, {"T11 [°C]", Text.Clean, type text}, {"T21 [°C]", Text.Clean, type text}, {"T31 [°C]", Text.Clean, type text}, {"T12 [°C]", Text.Clean, type text}, {"T22 [°C]", Text.Clean, type text}, {"T32 [°C]", Text.Clean, type text}, {"T01 [°C]", Text.Clean, type text}, {"T02 [°C]", Text.Clean, type text}, {"FM1 [slm]", Text.Clean, type text}, {"FM2 [slm]", Text.Clean, type text}, {"FM3 [slm]", Text.Clean, type text}}), #"Removed Alternate Rows" = Table.AlternateRows(#"Cleaned Text",0,5,1), #"Changed Type" = Table.TransformColumnTypes(#"Removed Alternate Rows",{{"System", type text}, {"Date&Time", type datetime}, {"P0 [mbar]", Int64.Type}, {"P1 [mbar]", Int64.Type}, {"P3 [mbar]", Int64.Type}, {"P7 [mbar]", Int64.Type}, {"PS160 [mbar]", Int64.Type}, {"P190 [mbar]", Int64.Type}, {"P220 [mbar]", Int64.Type}, {"Q1 [ppb]", Int64.Type}, {"Q2 [ppb]", Int64.Type}, {"Q3 [ppb]", Int64.Type}, {"Q4 [ppb]", Int64.Type}, {"T11 [°C]", type number}, {"T21 [°C]", type number}, {"T31 [°C]", type number}, {"T12 [°C]", type number}, {"T22 [°C]", type number}, {"T32 [°C]", type number}, {"T01 [°C]", type number}, {"T02 [°C]", type number}, {"FM1 [slm]", Int64.Type}, {"FM2 [slm]", Int64.Type}, {"FM3 [slm]", Int64.Type}}), DateFilter = Table.SelectRows(#"Changed Type", each [#"Date&Time"] >= StartDate and [#"Date&Time"] <= EndDate), #"Removed Columns1" = Table.RemoveColumns(DateFilter,{"System"}) in #"Removed Columns1"
Top Optimization Tips to Cut Refresh Time
Let's go through the biggest bottlenecks in your current query and fix them step by step:
Filter EARLY, not late
Right now you're converting every single .dif file to CSV, expanding all data, then filtering bySystemToChooseand date range. That's like boiling an entire lake just to get a cup of water! Flip the order: filter the files you need first (based on System) before doing any binary conversion or data expansion. This cuts out processing 100% of irrelevant files right off the bat.Ditch the unnecessary grouping/expanding
YourGrouped RowsandExpanded Countsteps are totally redundant. You don't need to group by System just to expand it again—keep the System column attached to each file's data from the start, then filter directly. This saves memory and avoids extra table operations.Delay type conversions and text cleaning
You're cleaning text and converting types before filtering out invalid rows and date ranges. Do all your filtering first, then clean and convert only the data you're actually keeping. Processing fewer rows means less work for Power BI.Optimize date filtering
Converting every "Date&Time" entry to datetime first, then filtering, is slower than filtering on the text version first (assuming your date format is consistent, e.g.yyyy-MM-dd HH:mm:ss). Filter the text dates to your range, then convert to datetime—this reduces the number of conversions you need to do.Tweak binary handling
You're already usingBinary.Buffer([Content])which is great for caching, but make sure you're only applying it to the files you've already filtered. Also, double-check thatColumns=23is consistent across all your .dif files—if some have fewer columns, Power BI will waste time detecting schema changes.Local storage hacks (if you can)
If your company allows it, pre-convert those .dif files to Parquet format (it's a compressed columnar storage that Power BI reads way faster than binary .dif). Also, move the source folder to an SSD if you're on a mechanical drive—disk read speed is a huge bottleneck here.
Revised Query Example
Here's how your query might look after applying these changes:
let // Get all .dif files first Source = Folder.Files("Q:\Objekt\ABC\DEF\XYZ\1. Source Data"), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".dif")), // Extract System from Folder Path FIRST #"Inserted System" = Table.AddColumn(#"Filtered Rows", "System", each Text.BetweenDelimiters([Folder Path], "\", "\\", 6, 0), type text), // FILTER BY SYSTEM PARAMETER BEFORE PROCESSING ANY BINARY DATA #"Filtered by System" = Table.SelectRows(#"Inserted System", each [System] = SystemToChoose), // Keep only necessary columns early #"Removed Unneeded Columns" = Table.SelectColumns(#"Filtered by System",{"Content", "System"}), // Convert only filtered files to CSV BinaryToCSV = Table.AddColumn(#"Removed Unneeded Columns", "Custom", each Csv.Document(Binary.Buffer([Content]),[Delimiter="#(tab)", Columns=23, Encoding=1200, QuoteStyle=QuoteStyle.None])), #"Removed Content Column" = Table.RemoveColumns(BinaryToCSV,{"Content"}), // Expand CSV data + rename columns in one step #"Expanded & Renamed Custom" = Table.ExpandTableColumn(#"Removed Content Column", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}, {"Date&Time", "P0 [mbar]", "P1 [mbar]", "P3 [mbar]", "P7 [mbar]", "PS160 [mbar]", "P190 [mbar]", "P220 [mbar]", "Q1 [ppb]", "Q2 [ppb]", "Q3 [ppb]", "Q4 [ppb]", "T11 [°C]", "T21 [°C]", "T31 [°C]", "T12 [°C]", "T22 [°C]", "T32 [°C]", "T01 [°C]", "T02 [°C]", "FM1 [slm]", "FM2 [slm]", "FM3 [slm]"}), // Filter out invalid rows FIRST (before cleaning/converting) #"Filtered Invalid Rows" = Table.SelectRows(#"Expanded & Renamed Custom", each [#"P0 [mbar]"] <> "" and [#"P0 [mbar]"] <> "mbar" and [#"P0 [mbar]"] <> "P_0"), // Remove alternate rows next #"Removed Alternate Rows" = Table.AlternateRows(#"Filtered Invalid Rows",0,5,1), // Filter DATE RANGE AS TEXT FIRST (adjust if your date format differs) #"Filtered Date Range (Text)" = Table.SelectRows(#"Removed Alternate Rows", each [#"Date&Time"] >= Text.From(StartDate) and [#"Date&Time"] <= Text.From(EndDate)), // Now clean text ONLY for remaining rows #"Cleaned Text" = Table.TransformColumns(#"Filtered Date Range (Text)",{{"System", Text.Clean, type text}, {"Date&Time", Text.Clean, type text}, {"P0 [mbar]", Text.Clean, type text}, {"P1 [mbar]", Text.Clean, type text}, {"P3 [mbar]", Text.Clean, type text}, {"P7 [mbar]", Text.Clean, type text}, {"PS160 [mbar]", Text.Clean, type text}, {"P190 [mbar]", Text.Clean, type text}, {"P220 [mbar]", Text.Clean, type text}, {"Q1 [ppb]", Text.Clean, type text}, {"Q2 [ppb]", Text.Clean, type text}, {"Q3 [ppb]", Text.Clean, type text}, {"Q4 [ppb]", Text.Clean, type text}, {"T11 [°C]", Text.Clean, type text}, {"T21 [°C]", Text.Clean, type text}, {"T31 [°C]", Text.Clean, type text}, {"T12 [°C]", Text.Clean, type text}, {"T22 [°C]", Text.Clean, type text}, {"T32 [°C]", Text.Clean, type text}, {"T01 [°C]", Text.Clean, type text}, {"T02 [°C]", Text.Clean, type text}, {"FM1 [slm]", Text.Clean, type text}, {"FM2 [slm]", Text.Clean, type text}, {"FM3 [slm]", Text.Clean, type text}}), // Finally convert types for only the data we're keeping #"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"System", type text}, {"Date&Time", type datetime}, {"P0 [mbar]", Int64.Type}, {"P1 [mbar]", Int64.Type}, {"P3 [mbar]", Int64.Type}, {"P7 [mbar]", Int64.Type}, {"PS160 [mbar]", Int64.Type}, {"P190 [mbar]", Int64.Type}, {"P220 [mbar]", Int64.Type}, {"Q1 [ppb]", Int64.Type}, {"Q2 [ppb]", Int64.Type}, {"Q3 [ppb]", Int64.Type}, {"Q4 [ppb]", Int64.Type}, {"T11 [°C]", type number}, {"T21 [°C]", type number}, {"T31 [°C]", type number}, {"T12 [°C]", type number}, {"T22 [°C]", type number}, {"T32 [°C]", type number}, {"T01 [°C]", type number}, {"T02 [°C]", type number}, {"FM1 [slm]", Int64.Type}, {"FM2 [slm]", Int64.Type}, {"FM3 [slm]", Int64.Type}}), // Remove System column if not needed #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"System"}) in #"Removed Columns1"
Quick Notes
- The date text filter might need adjustment based on your actual "Date&Time" format—if it's not sortable as text, skip that step and filter after converting to datetime, but try the text filter first if possible.
- If you can pre-process the .dif files to Parquet, replace
Csv.DocumentwithParquet.Documentafter conversion—this will give you a massive speed boost.
内容的提问来源于stack exchange，提问作者MmVv
