A Fine Slice Of SQL Server
Matt Allington goes to the bargain bin:
Paginated Reports have been available in Power BI since 2019. They serve an important purpose, but they are not easy for the average business user to learn, plus they require Power BI Premium to use. In my blog and video today, I will show you how you can use Excel as a substitute for Paginated Report Builder to build simple paginated reports from your Power BI Desktop data model.
Click through to see how.
Comments closedEd Hansberry makes a list and checks it twice:
Lists in Power Query are something many people know nothing about. Power Query uses them all the time even though you may not realize it, so if you add some List knowledge to your quiver, you’ll be able to kick your Power Query skills up a notch.
In my work, I often see the need for counting words, especially today with so much online data. Perhaps you want to ensure your Amazon product listings have a maximum number of words in the descriptions or you want to count the words in a podcast. The method I’m going to show you will count anything in your data, so you can apply this pattern to any of your datasets.
Also known as map and reduce (but not quite MapReduce).
Comments closedGilbert Quevauvilliers continues a series on determining who has access to what reports in Power BI:
This is the second part in my blog post series showing you how I created the Power BI Reports list.
In this blog post I am going to show you how I used PowerShell to get all the information of the App Names, reports and users that have permissions in the different Apps.
Users can get access to Power BI reports directly via the Share method, as well as via an App. I did this to ensure that I did not miss any reports that a user did have access to, and I could not show it!
Click through for the script, as well as an an explanation of how it all works.
Comments closedI still get a lot of questions from various Power BI developers about table storage modes and how table storage modes affect an entire model’s type. Here is a post to summarize all table storage modes / model types.
The following table storage options apply when creating a Power BI model.
There’s a brief summary in the tables, as well as additional notes below them.
Comments closedImke 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 closedChris 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 closedGilbert 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 closedShabnam 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 closedImke 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 closed