A Fine Slice Of SQL Server
Imke Feldmann provides some answers:
Have you ever come across the case where you open your dataflow and find 2 additional steps at the end of your query? Why are Power Platform Dataflows adding steps automatically at the end?
It will be a step that replaces errors on certain columns followed by a step that transforms these columns into text.
Read on to understand why.
Comments closedTeo 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 closedAdam 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 closedMeagan 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 closedChris Webb looks at a new addition to Power Query:
Anyone with previous experience of Table.Buffer will see that BufferMode.Eager is the existing behaviour, but what is BufferMode.Delayed for?
It turns out that it’s there to make development faster.
Read on for the answer and what it does.
Comments closedReza 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 closedGauri Mahajan tries out the Q&A visual:
The speed at which the options for data hosting, data processing and data management keep growing, the options for data consumption have also been growing at the same pace. Traditionally, applications and reports used to be the most common and most frequent means of consuming data. As data consumption means matured with time, chatbots, analytics engines, machine learning and artificial intelligence tools and many others. Traditionally, to explore the data, some of the common mechanisms have been using database query languages, preparation of reports by report designers and data exploration in a self-service manner by power users. With the evolution of capabilities like machine learning, artificial intelligence, natural language processing and others, some of the popular and modern methods of data exploration includes natural language-based data analysis, voice-enabled data exploration using smart devices, computer vision-based data analysis, etc. While many of these methods are highly sophisticated and need user training for a user to employ these data exploration methods, natural language-based data exploration is one of the most popular data exploration methods. This method is offered out-of-box by many reporting tools including Tableau and Power BI as well.
The Q&A visual is a really cool concept which works a surprising amount of the time. The problem is that when it doesn’t work, it feels like pushing a string: no matter what you do, it just doesn’t quite do what you need it to.
Comments closedImke Feldmann has another Excel function to convert:
TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but in here I want to share my M version for Power Query for it.
Read on for the function and how you can use it.
Comments closedMarco Russo does a bit of transformation:
In matrix visuals, Power BI usually hides rows where all the measures return a blank value. To leverage this behavior or simply to change the visualization of a measure depending on its result, you might want to achieve one of the following:
– Transforming a blank result to zero: this is covered in the article, How to return 0 instead of BLANK in DAX.
– Transforming a zero result to blank: this is the scenario described in this article.
Click through to see what the process looks like.
Comments closedImke Feldmann translates another Excel function:
If you are looking for the Excel equivalent of the WEEKNUM function for Power Query this article is for you.
As this is also a function that has many regional options, I was lucky to find an algorithm that I could use for its main part here: M functions to convert between ISO 8601 Week & Year ⇄ dates (e.g.,
2014-12-29⇄"2015-W01-1") (github.com)
Read on for the code and details on function parameters.
Comments closed