• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to use table.buffer PLEASE HELP|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to use table.buffer PLEASE HELP|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to use table.buffer PLEASE HELP
sp_PrintTopic sp_TopicIcon
How to use table.buffer PLEASE HELP
Avatar
simon bernick

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
1
March 8, 2021 - 6:40 am
sp_Permalink sp_Print sp_EditHistory

I am working on the Covid-19 vaccination effort and I could really use some help as I do not normally write code. I am using power query to load data from a set of CSV files, but the data refreshes are taking too long.  I have turned off privacy settings, stopped background refresh, and combined all my queries into one query (below) to speed things up,  but its still taking 4 minutes to load 600k lines (500MB of CSV data).  

I want to use table.buffer to speed this up, but I can not figure out where to put it.  If I try putting it in front of the source like this table.buffer(Source = Folder.Files("T:\Restricted\2_Vaccination_Project\Reporting\Simon\Power Pivot Import"))  then I get an error message "Token Equal Expected".  

Please tell me where I am supposed to put table.buffer (please be explicit, I have not used power query before today)!

 

let
WithAddedKey =
Table.AddKey(
#"Names",
{"User Name"},
true),
Source = Folder.Files("T:\Restricted\2_Vaccination_Project\Reporting\Simon\Power Pivot Import"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Extension], ".csv")),
#"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File from Grab&Clean Data", each #"Transform File from Grab&Clean Data"([Content])),
#"Renamed Columns2" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns2", {"Source.Name", "Transform File from Grab&Clean Data"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File from Grab&Clean Data", Table.ColumnNames(#"Transform File from Grab&Clean Data"(#"Sample File (2)"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column2",{"Team", "Source.Name", "Invitee Name", "Invitee Time Zone", "Invitee accepted marketing emails", "Event Type Name", "Location", "Canceled By", "Cancellation reason", "Question 1", "Question 2", "Question 6", "Response 6", "Question 7", "Response 7", "UTM Campaign", "UTM Source", "UTM Medium", "UTM Term", "UTM Content", "Salesforce UUID", "Event Price", "Payment Currency", "Guest Email(s)", "Response 2", "Question 3", "Response 3", "Question 4", "Response 4", "Question 5", "Response 5"}),
#"Split Start Date/Time" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Start Date & Time", type text}}, "en-US"), "Start Date & Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Start Date & Time.1", "Start Date & Time.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Start Date/Time",{{"Start Date & Time.1", type date}, {"Start Date & Time.2", type time}}),
#"Split Created Date Time" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Event Created Date & Time", type text}}, "en-US"), "Event Created Date & Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Event Created Date & Time.1", "Event Created Date & Time.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Created Date Time",{{"Event Created Date & Time.1", type date}, {"Event Created Date & Time.2", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Response 1", "Phone 2"}, {"Start Date & Time.1", "Start Date"}, {"Start Date & Time.2", "Start Time"}, {"Event Created Date & Time.1", "Created Date"}, {"Event Created Date & Time.2", "Created Time"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns"),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"User Name"}, Names, {"User Name"}, "Names", JoinKind.LeftOuter),
#"Expanded Names" = Table.ExpandTableColumn(#"Merged Queries", "Names", {"Clean Name"}, {"Clean Name"}),
#"Remove Unclean Name" = Table.RemoveColumns(#"Expanded Names",{"User Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Remove Unclean Name",{"Clean Name", "Invitee First Name", "Invitee Last Name", "Invitee Email", "Text Reminder Number", "Phone 2", "Start Date", "Start Time", "End Date & Time", "Created Date", "Created Time", "Canceled"}),
#"Uppercased Text" = Table.TransformColumns(#"Reordered Columns",{{"Canceled", Text.Upper, type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Uppercased Text", each [Canceled] = "FALSE"),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [Clean Name] <> "Test"),
#"Grouped Rows" = Table.Group(#"Filtered Rows2", {"Clean Name", "Start Date", "Created Date"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
March 9, 2021 - 5:50 am
sp_Permalink sp_Print

Hello,

You need to put the Table.Buffer after the equal sign, as shown below.
This post has been linked to previously, so take a look. It is not always so that buffering the data improves the query load time.

Source = Table.Buffer(Folder.Files("T:\Restricted\2_Vaccination_Project\Reporting\Simon\Power Pivot Import"))

Br,
Anders

Avatar
simon bernick

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 8, 2021
sp_UserOfflineSmall Offline
3
March 9, 2021 - 3:17 pm
sp_Permalink sp_Print

It looks like buffering the source won't help me because its a folder not a file.  How do I get it to buffer each file in the folder, not the folder itself? Also I tried as you suggested, buffering a few different steps in the query (see below for an example) but when I did that the query would never finish refreshing (it loaded all the data, then just sat there doing nothing).

 

thebufferedTable = table.buffer(#"Invoke Custom Function2")

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
March 15, 2021 - 1:16 pm
sp_Permalink sp_Print

Hi Simon,

The use of Table.Buffer isn't very well explained by Microsoft.  Sometimes it makes things quicker, sometimes it doesn't.  In your case as you are loading files from a folder, there's no easy way to buffer each file.  And there's no guarantee that doing that would makes things significantly quicker anyway.

Without actually having the code and files to hand for testing, it makes it difficult to pinpoint the issue.

I do see that you have a Merge in you steps, and a Grouping. Both can affect performance.

Your Merge is with another table called Names that we don't know anything about.  What's in that? Where is it getting its data from?

What I would do is work through the query step by step until you hit the step that slows it down.

Start with #"Merged Queries".  Copy your original query into another file then remove all steps from #"Merged Queries" down.  Run that query. How long does it take?  Then add #"Merged Queries" and repeat for every step.

You could try buffering both tables in the merge like this

#"Merged Queries" = Table.NestedJoin(Table.Buffer(#"Removed Duplicates"), {"User Name"}, Table.Buffer(Names), {"User Name"}, "Names", JoinKind.LeftOuter)

Other things to consider.  You're loading the data from a network drive, try moving the files to a local drive and see if that speeds things up.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Chandler Davis, Valentyn Kristioglo
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27212

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.