When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.
Posts in Category ‘Power Query’
Improve File Import from SharePoint in Power BI and Power Query
When you use the UI to import files from SharePoint, you’ll end up with the Sharepoint.Files function. This function can become fairly or super slow when you use it on large SharePoint sites. This is due to the fact, that it will retrieve metadata for ALL files that lie on the site. Meaning: The root site whose URL you have to enter as the function argument. So I’ve developed a better way for File import from SharePoint.
Alternative
A faster alternative is the function SharePoint.Contents. This function will read much less metadata and that seems to make it faster. But it comes with a different navigation experience: It basically only allows to select files from one folder.
Therefore I’ve created 2 functions that overcome those limitations.
Convert DateTime to ISO 8601 date and time strings in Power Query
Often, when querying APIs it is required to enter date and time filters in ISO 8601 format . Today I show a quick way to convert DateTime to ISO 8601 string, based on an ordinary DateTime field according to the following pattern:
2020-10-11T15:00:00-01:00
This represents the 11th October 3pm in UTC -1 timeszone.
Steps to convert DateTime to ISO 8601
If I enter:
#datetime(2020,10,11,12,0,0)
into the formula bar, it will be converted to :
11/10/2020 12:00:00
Comparing to the desired ISO format the year, month and days are in the wrong order. So using the universal Text.From function will not return the correct result.
Retrieve header fields like response status from Web.Contents in Power BI and Power Query
Many Power Query function not only return their values as advertised in their function documentation, but on top of that a metadata record. This record is like tag that holds additional information about the returned main value (for more details about this, please check out my friend Lars Schreiber’s article about it).
Useful metadata for the Web.Contents function
Today I discovered that the function Web.Contents delivers a really nice record with a couple of useful information. To retrieve header fields, you have to use the Value.Metadata function, like so for example:
Interesting metadata from the Web.Contents – function
This might help for some advanced web query tasks.
How to use
If you want to use this in production, you’d probably branch out the logic. So first use Web.Contents and keep that result in a column or variable. Then add another column that references it and return the metadata record.
Apply the logic check on it and create a last column where you finally parse the content from the binary that Web.Content has returned.
Enjoy & stay queryious 😉
Transform a query into a function in Power Query and Power BI
In my previous blogpost I’ve described a method how to extract a substring that follows a certain pattern from a string. In this post I show how to transform a query into a function that can be applied to many rows of a table.
Video how to transform a query into a function
Please check the video for detailed steps. In there I also show how to modify the code. It shall also detect strings with a sequence of just 8 numbers. In the original query, those had to be followed by a minus sign and another number:
Extract pattern string and numbers from text using List.Accumulate in Power Query
A typical task when cleaning data is to extract substrings from string that follow a certain pattern. In this post I’m going to describe a method that uses the List.Accumulate function for it. Extract a pattern string.
Task
I have to extract a payroll key from a description field that starts with 8 number, followed by an “-” and another number.
aölsfdk0125-fds da12345678-0asdf
So I’m after the 12345678-0.
Plan
I plan to approach this by