Power BI has a somewhat hidden feature you may not have know existed - or maybe you knew about it but haven't given it a thought to try it out (you should!). It is truly magical as it makes parsing data extremely simple, and can save you potentially hours of work.
How many times have you tried getting data from a web page, only to crawl through layers and layers of endless HTML tables, bodies, texts, etc.. I've been there, and sometimes it seems endless to the point of giving up. I'm here to tell you this feature will save you from all your frustrations and broken keyboards and mice (mouses?).
So how do you do this?
Instead of the normal route Power Query takes you through on the Navigator window when previewing web data results, you:
Simply click the "Add Table Using Examples" button.
From there, it'll bring up a new window where you can add values from the web page.
After you enter a couple values from the web page, Power Query will guess the rest (and usually guess correctly). But do keep in mind it is just a guess, albeit a mathematical guess, so be sure to confirm the values before moving on. If any aren't the values you want, you can simply type over it, and Power Query will take that updated value and update the algorithm behind the magic.
Data Already Imported
This will also work on a data column that you have already imported into the data model. You can find the feature under "Add Column" -> "Column From Examples".
What's going on under the hood?
Depending on the complexity, Power Query will insert varying M functions to achieve the desired results. In the above GIF, PQ just ended up extracting the first character with Text.Middle("Gas", 0,1).
What about a slightly more complicated example. Take this data set: Let's say I want to extract the number portion out of the "File Detail" column.
For example, I want to extract 2549, blank, 2549, blank....., 5780, 1000, etc...
BTW these are actually cell phone channels for RF engineering.
What would Power Query do in this instance? Well, it will end up running the Text.BetweenDelimiters() function - slightly more complicated than before.
= Table.AddColumn(
#"MyTable",
"Channel",
each Text.BetweenDelimiters([File Detail], "_", "_", 6, 0),
type text)
The function will extract the first item (0 index) in an array of characters after the 6th occurrence of the "_" character.
What about the Web Example?
Surely this one is complex... In the above snippet, to get that first column of data for the star of Antares, PQ ends up using the following code:
Html.Table(Source, {{"Column1", "TH[style*=""text-align\:left""]"}})
Html.Table simply returns a table from a given HTML document. Now, I'm not familiar with HTML at all, but somehow PQ is able to identify the column I wanted with TH[style*=""text-align\:left""]. I searched the HTML document for anything that resembles this, but didn't find anything. I'll leave this function as -- Magic -- ultimately, it just works and that's all we care about.
There's another great blog which dives a bit deeper into this part of PQ at poweredsolution.co.
Cheers!
