Image you have a string like so: “10.ROADBIKES.423/01” and would like to extract only “ROADBIKES”.
Power Query actually has a function for this purpose:
Click through to learn what that function is and how it works.
Comments closedCategory: Power BI
Chris Webb lets us know about some new functionality in Power Query:
It’s a new option on the Excel.Workbook function called InferSheetDimensions. Here’s what the docs for the Excel.Workbook function say:
Can be null or a logical (true/false) value indicating whether the area of a worksheet that contains data should be inferred by reading the worksheet itself, rather than by reading the dimensions metadata from the file. This can be useful in cases where the dimensions metadata is incorrect. Note that this option is only supported for Open XML Excel files, not for legacy Excel files. Default: false.
Read on to understand when that might happen.
Comments closedEd Hansberry knows what time it is:
You may need to write a Power BI report that works with Unix Epoch Time, converting either to or from it. Epoch Time is simply the number of seconds that have passed since January 1, 1970, at 12:00 am UTC. So at 1 am of that date, Epoch Time would be 3600, since 3,600 seconds have transpired. Today Epoch Time is around 1610644465, or 1,610,644,465 to make it easier to read, though you will never see the commas in a database.
Converting to and from this time format is deceptively simple, as long as you understand a few functions in Power Query and the concept of Duration.
Read on for a demo.
Comments closedReza Rad has a workaround for us:
If you use a streaming dataset in Power BI, you cannot download the Power BI file, and you cannot open it using Power BI Desktop. This means that you are limited not to use calculations in a streaming dataset. However, there is a small trick which you can use and can be helpful. I will show you that in this article and video.
Click through for the article, which includes the video.
Comments closedGilbert Quevauvilliers needs things to be faster:
I was recently getting an error when refreshing an Excel file that was stored in SharePoint online.
This got me thinking what the issue is and is there a way to improve it and YES there is as detailed below.
When I investigated this further, I found that my 17kb Excel file was using at least 616kb of data when being refreshed in Power BI Desktop. Whilst this is a relatively small amount this is 36x larger than the file size. This led me to believe that it is possibly being read multiple times, but some other things might be going on!
Spoilers: there were things going on.
Comments closedReza Rad has an update to the Power BI Helper:
Happy New Year. We wish this be a year full of happiness and joy for all of you. We are glad to let you know that version 12 of Power BI Helper is now available to download with the below new features;
– Detecting the storage mode of tables (DirectQuery, Dual, Import)
– Detecting the connection mode of the file (Import, DirectQuery, Live Connection, Composite/Mixed)
– Report level measures
– Compare two files based on their report level measures
– Analyzing the visualization just by selecting the model
– Feedback form
– Documentation of all the above new information
– bug fixes
Click through for the full changelog.
Comments closedImke Feldmann has some misspellings:
A typical problem with data that has been created by manual entries is that category values are often misspelled or missed. So in this article I’m showing a very powerful technique on how to deal with this problem to clean up dirty category data. It was inspired by the “Preppin’ data” challenge whose instructions you can read here.
Read on for the solution.
Comments closedBenni de Jagere does some digging:
The other day, I was chatting with one of my clients about Premium Per User, and I gave them the practical guidance to not build any production level dependencies based on PPU features or workspaces, until some of the unknowns have been cleared up. If there’s end users relying on this for their actual daily job, then I’m calling it a production level dependency. Right now, these are preview features, and this client is not actively monitoring changes in the Power BI Landscape.
Shortly after, I got a message that some of their business users did build actual production reports and dataflows in PPU workspaces. And, they were not sure who in the company actually has access to PPU. And that’s where chase down the rabbit hole began
I imagine that this will get easier over time but right now, it doesn’t seem that simple.
Comments closedChris Webb troubleshoots an issue:
Some time ago I wrote a pair of popular posts about using the Query and RelativePath options of the Web.Contents function in Power Query and why they are important for dataset refresh. I have recently learned something extra about this subject which merits a new post, though: how to handle multiple URL query parameters with the same name.
It’s interesting to see how Power Query handles this, as there’s no defined standard behavior. Some renderers give you just the first item, some just the last, and some (like IIS + .NET) give you back a list of all items when you have a query string like ?param1=x¶m1=y¶m1=z.
Marc Lelijveld wants to pull some metrics:
In the Power BI service, you can easily look at refresh times for an individual dataset or dataflow. There are many different reasons why these metrics are important to you as a dataset or dataflow owner. For example, you may bump into refresh time-outs or unfortunate errors. There are many good reasons to think about why you want to have more insights in your refresh metrics.
Having that said, it can be a pain to look at these metrics every day. Power BI already offers a way to send automatic notification in case of a refresh failure. Though, I would personally prefer to have more insights in all my refresh metrics, whether they are failing or succeeding.
In this blog I want to share a way how you can export all refresh metrics for your datasets and dataflows using a PowerShell script.
Click through to see how and to get a copy of the script.
Comments closed