Forum Discussion

newtoPowerBI's avatar
newtoPowerBI
New Member
8 years ago

Query Performance Slow 2,500 KB File Reading as 6GB

Hello,   I need help figuring out why a small .csv file is coming into Power Query and reading as 6GB. I have a few merged queries in my workbook so I am wondering if that is whats causuing the slo...
yoava's avatar
yoava
Regular Visitor
8 years ago

Hi Anonymous newtoPowerBI,

 

I am also experianceing unusual performance degredataion with small files being read as very large ones

I have a 2,500 KB csv that shows the same behaviour described above. 

 

 

 

let
    Source = Csv.Document(File.Contents("C:\input.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    #"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Trimmed Text" = Table.TransformColumns(#"Use First Row as Headers",{{"ADD_CONFIG", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"ADD_CONFIG", Text.Clean, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each [ADD_CONFIG] <> null and [ADD_CONFIG] <> ""),
    #"Parsed XML" = Table.TransformColumns(#"Filtered Rows",{{"ADD_CONFIG", Xml.Tables}}),
    #"Expanded ADD_CONFIG" = Table.ExpandTableColumn(#"Parsed XML", "ADD_CONFIG", List.Union(List.Transform(#"Parsed XML"[ADD_CONFIG], each Table.ColumnNames(_))))
in
    #"Expanded ADD_CONFIG"

 

 

Using some trial and errors, i figured the performance degradation is directly related to the following expression:

 

    #"Expanded ADD_CONFIG" = Table.ExpandTableColumn(#"Parsed XML", "ADD_CONFIG", List.Union(List.Transform(#"Parsed XML"[ADD_CONFIG], each Table.ColumnNames(_))))

 

If i replace it with an actual comma seperated list (generated using the same List.Union(List.Transform)), the expansion is immediate:

 

= Table.ExpandTableColumn(#"Parsed XML", "ADD_CONFIG", {"foobar0","foobar1","foobar10","foobar100","foobar101","foobar102","foobar103","foobar104","foobar107","foobar108","foobar109","foobar11","foobar110","foobar112","foobar12","foobar13","foobar14","foobar15","foobar16","foobar17","foobar18","foobar19","foobar2","foobar20","foobar21","foobar22","foobar23","foobar24","foobar25","foobar27","foobar28","foobar29","foobar3","foobar30","foobar31","foobar32","foobar33","foobar34","foobar35","foobar36","foobar37","foobar38","foobar39","foobar4","foobar40","foobar41","foobar42","foobar43","foobar44","foobar45","foobar46","foobar47","foobar48","foobar49","foobar5","foobar50","foobar51","foobar52","foobar53","foobar54","foobar55","foobar56","foobar57","foobar58","foobar59","foobar6","foobar60","foobar61","foobar62","foobar63","foobar64","foobar65","foobar67","foobar68","foobar69","foobar7","foobar70","foobar71","foobar72","foobar73","foobar74","foobar75","foobar76","foobar77","foobar78","foobar79","foobar8","foobar81","foobar82","foobar83","foobar84","foobar85","foobar86","foobar87","foobar88","foobar89","foobar9","foobar91","foobar92","foobar93","foobar94","foobar95","foobar96","foobar97","foobar98","foobar99"})

 

 Note that this expansion does not create additional rows, only additional columns.