Category: Power BI

Marc Lelijveld shows how you can embed Power BI Dataflow refresh times in your Power BI reports:

But maybe you want to visualize this as part of your report as well. With a really simple piece of Power Query code you can easily generate a date/time at the moment that your dataset is processed. Kasper de Jonge wrote a blog post on that, so I’m not going to elaborate on that. However, when we add this as a separate entity to each dataflow, it results in a last successful refresh date/time for each dataflows.

Since each dataflow will be refreshed on it’s own, likewise as a dataset, the entity with your last date/time will always the last date/time for the whole dataflow, no matter how many entities are in there.

Read on to see how to combine and display these refresh times.

Comments closed

Melissa Coates explains why you should create reports in Power BI Desktop rather than directly through the Power BI Service:

I always recommend to Power BI authors that report creation & editing should happen in Power BI Desktop and to just ignore the edit capability in the Power BI Service. Usually my reasons are concerned with (a) which version is the latest version and (b) having versioning history, and (c) reducing the risk of someone overwriting someone else’s work because two different reporting editing/publishing options are in use. These are very valid concerns, but I’ve discovered one additional problem that could easily confuse some people: (d) the side effects experienced if you download then re-upload a web-created report. Let me explain…

Read on for that explanation.

Comments closed

Alberto Ferrari shows how you can replace bidirectional filters with visual filters in Power BI and improve visual quality:

The noticeable thing about the behavior of the slicer is that the two matrices are showing only the brands and colors purchased by Amanda. Yet, the Color slicer is still showing all the colors, even though we know Amanda only purchased three colors: Grey, Silver and White.

The reason is that the matrices, like most Power BI visuals, hide rows if the measure they are showing produces a blank. Because Amanda did not buy any pink product, the value of Sales Amount for Pink results in a blank, therefore the matrix removes the pink color from its result. Prior to the May 2019 release of Power BI, slicers did not display this behavior because slicers did not have a measure to evaluate – they would only show a list of values from a column; Moreover, visual-level filters were not allowed in slicer visuals whereas they were available in other visuals such as charts, tables, and matrices.

Read on to see how to do this.

Comments closed

Matt Allington shows how you can pivot text data from an Excel spreadsheet using Power Query:

It is very common to need to transform data from one “shape” to another “shape” before it can be used inside Power BI for analysis (although many beginners don’t realise this).   One such example is shown below, where the data in the table on the left hand side needs to be transformed into the table on the right hand side.  As you can see on the left, column A contains the attribute and column B contains the value of the attribute.  Every 4 lines of data is 1 record.  This specific problem is very common problem when your only source of data is from an extract (eg csv) from some other system, particularly older systems where you can’t change the format of the data extract.

This is a clever solution.

Comments closed

Erik Svensen shows how you can find help for M functions without leaving the Power Query editor:

One of the things you should know when working with PowerQuery is that you can get a list of all functions in M by adding a blank query and use the #shared expression to get all the functions.

Read on for an example and an even easier way to get information for a single function.

Comments closed

Chris Webb goes over an issue with an attempt to expand out a set of folders in M:

The approach I took was the one that seemed natural to me at the time:

1. Use the Folder data source to connect to the folder containing the image files
2. Define a function called SplitText that takes a long piece of text and splits it up into a list of text values no longer than 30000 characters
3. Call the function once per row on the table returned by step (1)
4. Use the Expand/Aggregate button to expand the new column created by step (3) and get a table with one row for each of the split-up text values

When I ran this query, though, I caught sight of something that is every Power Query developer’s worst nightmare:

Read on for more. Also, drop by to congratulate Chris on collecting a blue badge.

Comments closed