Press "Enter" to skip to content

Category: Power BI

Filling Values to the Right (or Left) in Power Query

Imke Feldmann has a new function:

The first function argument takes the table you want to apply the function on. The second argument is the list of column names that shall be filled up into empty values to the right. In the example in the function documentation, this is:  {“H1”, “H2”, “H3”}. The curly brackets define a list object in Power Query and its list elements must be put in quotes if they shall represent strings. So here the columns H1, H2 and H3 are included.
An optional 3rd argument can be used to fill to the left instead. You can fill in any value there, so once it is used, the fill will work to the left instead.

This might not be something you use on a daily basis but I will say I’ve run into situations in which having a function like this at hand would have been quite valuable.

Comments closed

Exponential Smoothing in Excel

Chris Webb starts an interesting series:

[This function] calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends.

Recently I started playing around with this function to see how it could be used with cube functions and since I learned so many interesting things I thought it would make a good series of blog posts.

Read on for an example of the normal way to use this function.

Comments closed

Using the Power BI Scanner

Gilbert Quevauvilliers sets scanners to On:

As mentioned in my previous blog post this is part 1 of the series where I am going to show you how to use the Power BI Scanner to get the App workspace data. I am also going to mention that the Power BI Scanner from PowerBI.Tips and Tommy Puglia (Twitter) has a wealth of other awesome information for your Power BI tenant.

Fortunately I do not have to go through all the steps on setting up and getting the Power BI Scanner data, you can do it by following the blog post already created with some amazing details here: Using the Power BI Scanner API to Manage Tenant’s Entire Metadata

Check out that article but Gilbert also has some nice tips.

Comments closed

Power BI Hybrid Table Q&A

Shabnam Watson shares some questions and answers:

Are Hybrid Tables tied to a developer license type?

No. Incremental Refresh and Hybrid tables are tied to workspace (dataset) type. They are set up in Power BI Desktop. A developer must have Pro or Premium Per User (PPU) license to publish the report to the service. See the next two paragraphs for workspace (dataset) limitations.

Click through for the FAQs and answers.

Comments closed

Power BI Dataflows and Storage Considerations

Teo Lachev has some things for us to consider:

Over the past few years, the BI industry has come up with new file formats, such as Parquet, ORC, and Avro, which are widely used today. To facilitate its vision for cross-industry data integration, Microsoft introduced a few years ago the Common Data Model (CDM) and CDM Folders. Power BI dataflows output CSV files to CDM folders and each table is saved in its own folder. You can bring your own data lake to directly access these files. If do so, you’ll find the following folder structure:

Although accessing the dataflow files might open all sorts of data integration scenarios, here are some things to watch for concerning the dataflow output:

Read on for five things.

Comments closed

Fun with DAX in Paginated Reports

Adam Aspin looks at paginated reports:

To conclude this short set of articles on using Power BI datasets as the source of data for paginated reports, I want to outline a few classic solutions to common challenges in paginated report development with DAX.

Clearly, I cannot recount every paginated report challenge that I have ever met (or heard of) when creating reports from a Power BI dataset. However, as a report developer, it helps to be aware of some of the standard solutions to the challenges that many users encounter.

This article uses the accompanying sample data (CarsDataWarehouse.pbix) as the basis for the DAX that you will use to solve these problems.

Click through for several tips, as well as some tricks.

Comments closed

Integrating the Intercom API with Power BI

Meagan Longoria brings in data from Intercom:

I needed to pull some user data for an app that uses Intercom. While I will probably import the data using Data Factory or a function in the long term, I needed to pull some quick data in a refreshable manner to combine with other data already available in Power BI.

I faced two challenges in getting this code to work:

1. Intercom’s API uses cursor-based pagination when retrieving contacts

2. I needed this query to be refreshable in PowerBI.com so I could schedule a daily refresh.

Read on to see how Meagan solved those problems.

Comments closed

Power BI Misconceptions

Reza Rad has a video (and article):

Misconception 1: Power BI is not an enterprise reporting tool, it is only good for self-service.

This is a misconception. And it is there because many people who have heard of Power BI, are not aware of the data modeling engine, the data transformation, and other main components of it. Maybe they just know Power BI as a visualization tool.

Power BI came to the market with the promise of binging data analysis to everyone using extra-ordinary self-service ability using Power BI Desktop and Power BI Service. However, Power BI itself is built on top of Microsoft enterprise data analysis toolset.

Read on for more information about this, as well as four other misconceptions.

Comments closed