Oftentimes, when working with Power Query, you’re dealing with Tables. But it also proves useful to add to your arsenal the nitty-gritty of working with records as well as with lists.
Tag: Power BI
How to do Lookup in Excel and Power Query
Lookup in regular Excel function
XLOOKUP function has been available to Office 365 and Excel 2021. For compatibility purposes with my workbook’s potential users, I shy away from using this function. Instead, I use the combination of
Why not VLOOKUP?
I’m not sure if there are any hardware efficiency benefit from using
HLOOKUP. But I use
MATCH primarily for two reasons:
- Ability to return the value of the column to the left of your lookup array (left-most column of the table array)
- Never worry about adding a column between the returned column and the lookup array. Remember, you indicate the column index number being the returned column from your table array? What if you’ve not touched your file for a long time, return to it and inserting a column in a table, forgetting that you’ve used that table as a table array as a lookup somewhere in your worksheet? You’ll certainly break your
How to use these formulas
Sorry, but you have to look somewhere else for the usage of these formulas.
Lookup in Power Query
Power Query Lookup Sample File
To better demonstrate the functions, it’s better if we refer to a sample file:
How to: Use Excel Power Query to Retrieve Data from Airtable
When you have your data neatly set up on Airtable, especially when they are particularly financial data and numbers, you would want to connect to them on Excel (or Power BI), and do the analysis (or charts) from the latter.
Excel’s Power Query can ETL (Extract, Transform, and Load) data from your Airtable database, and here’s how to even work around the 100 Rows Limit or Pagination.
What you need are:
- Base ID
- Table ID
- API Key
So I created a throwaway Airtable account for the API key, a throwaway workspace from that account, and and loaded the Simple Project Tracker base template, just for this demonstration.
You can get your API key from your account page (https://airtable.com/account):
You can get your Base ID and Table ID by going to the base and table you want to connect to and copy them from the URL: Continue reading
How to Use Power Query to Pull Data from a CloudFlare Protected Website
I run Microsoft Excel’s Power Query daily in order to pull data from the BAP website for the USD/PHP rates for the day, which in turn I save in an Airtable database as a record for that day.
This week, however, it seems that BAP has implemented CloudFlare DDoS Protection, which helps it to combat DDoS attacks. Good for BAP, they can minimize what we’ve experienced downtimes in the past. Bad for me, now I’m getting 503 error in my query.
So how do we deal with this in Power Query? A quick Google search yields not much of a result. There’s one posted in June 2017 on Mr. Excel forum, 3 years and no one bothered with an answer.Continue reading