cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lynk_RM
Helper I
Helper I

Efficient query to merge several SharePoint CSV files?

How can I make this query more efficient to cut down on load times? I have roughly 174 files (+1 daily) merged to provide 220k rows. The load times are becoming problematic. Please see the code below and help if possible:

 

 

 

let
    Source = SharePoint.Files("https://********.com/sites/******"),
    #"Filter Folder" = Table.SelectRows(Source, each ([Folder Path] = "https://***********.com/sites/****/***")),
    #"Filtered Rows" = Table.SelectRows(#"Filter Folder", each ([Extension] = ".csv")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column11", "Column2", "Column3", "Column4", "Column6"}, {"Custom.Column1", "Custom.Column11", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column6"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Date] <> "Date")),
    #"Filter Null Dates" = Table.SelectRows(#"Filtered Rows2", each [Date] <> null and [Date] <> ""),
    #"Change Date Type" = Table.TransformColumnTypes(#"Filter Null Dates",{{"Date", type date}}),
in
    #"Change Date Type"

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

That's a pretty straight forward query, so not much optimization potential.  One thing you could do is do your promote headers in the custom column step with the change below, which would eliminate that step and the Filter step to remove the rows with "Date".  You'll have to redo your Expand column steps with the new column names.

 

Replace  each Csv.Document([Content]) with  each Table.PromoteHeaders(Csv.Document[Content]))

 

That probably won't speed things up much.  Another thing to consider is to use that M code to create a Dataflow and then use that dataflow in your report instead.  That would move the refresh offline and your desktop file would refresh faster against the Dataflow.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Super User IV
Super User IV

That's a pretty straight forward query, so not much optimization potential.  One thing you could do is do your promote headers in the custom column step with the change below, which would eliminate that step and the Filter step to remove the rows with "Date".  You'll have to redo your Expand column steps with the new column names.

 

Replace  each Csv.Document([Content]) with  each Table.PromoteHeaders(Csv.Document[Content]))

 

That probably won't speed things up much.  Another thing to consider is to use that M code to create a Dataflow and then use that dataflow in your report instead.  That would move the refresh offline and your desktop file would refresh faster against the Dataflow.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.