This post is also available in: DeutschDeutsch

Combine multiple tables with UNION / UNION ALL in SQL Server, APPEND in Power Query or UNION in DAX

This post is also available in: Deutsch If you want to combine several tables, you have to ask yourself whether you should do this already in the data source such as SQL Server, in the processing of the data (ETL) in Power Query or in Power BI and, above all, where exactly there is a difference. First of all, the post is relatively detailed, for all of those who consider it too long (tl;dr) here is the comparison table: Duplicate entries remain Duplicate entries are removed Unequal number of columns Deviating column names Different order of the columns Combine more than 2 tables UNION in SQL Server With UNION ALL With UNION – x – x APPEND in Power Query x Possible with “Remove duplicates” x – x x UNION in DAX x Possible with “DISTINCT” – x – x Overview of the different possible combinations Data transformation in the data source or in the frontend? A question that comes up right at the beginning is, where should the joining of the tables take place?In fact, this cannot be stated in a generalized way, since it depends on a number of factors. But in most cases it is better […]
This post is also available in: Deutsch What at first sounds complicated and not very useful, can be found in reality from time to time. Recently the automated export of source data from another program in CSV format confronted me with this task. Daily folder for automated export Unfortunately, when exporting from the source system – apart from the target directory – very little could be adjusted. The result is that the automated export job daily creates a new folder with the current date, similar to the following example: In this example it is only necessary to load the files from the newest folder, because this folder contains the entire data. However, the file names also change with the daily export. The structure can be seen in the following picture: To load the latest data we have to: Find the most recent folder Save the date from the folder name as a variable or function Load the folder and files dynamically with a variable / function 1. Find the most recent folder To extract the information about the existing folders, we first have to load them into Power Query. Important here is that we create the folder name in […]
This post is also available in: Deutsch In yesterday’s poston the Power BI Blog, Microsoft announced the upcoming Power BI Premium for individual users. The advantages of Power BI Premium compared to a normal Pro Account are quite clear: 48 refreshes per data set per day compared to 8 refreshes with the Pro License, automated machine learning possibilities, cognitive services, the up to 16 times better performance (according to Microsoft) and the significantly higher limit for the size of data sets offer among others a huge added value. Premium up to now only affordable for large companies The problem so far is that Power BI Premium starts at 4’995 USD – noted per month. This corresponds to the financial expense of about 500 Power BI Pro licenses. Therefore, this license is a realistic alternative only from about 500 users on. For small and medium-sized companies this is hardly a worthwhile investment. Welcome Premium per User This is exactly where Power BI Premium pro User steps in. To say it in advance, the prices are not yet announced, but Microsoft calls it “uniquely affordable”. During the public preview, which starts at the beginning of November, it is even completely free […]
This post is also available in: Deutsch In times of Corona we have to take other ways to avoid the gathering of larger crowds.Such as Microsoft, which is setting this year’s Ignite from 22-24.09.2020 as an exclusively virtual event. For Europeans maybe not even the worst decision 😉 You can register on the Ignite website: According to the PBI blog the following sessions are about Power BI: Live Sessions Microsoft Power Platform: Fill the app gap and supercharge organizational agilityLive Tuesday Sept 22: 9:30 – 9:50 AM PDTReplay Sept 22: 5:30 – 5:50 PM PDTReplay Sept 23: 1:30 – 1:50 AM PDT Deliver insights where decisions are made with Power BILive Tuesday Sept 22: 12:15 – 12:45 PM PDTReplay Sept 22: 8:15 – 8:45 PM PDTReplay Sept 23: 4:15 – 4:45 AM PDTLive Ask the ExpertSept 22: 1:00 – 1:30 PM PDTSept 22: 9:00 – 9:30 PM PDT Building systems of insights for enterprise scale with Power BI and AzureLive Tuesday Sept 22: 3:15 – 3:45 PM PDTReplay Sept 22 11:15 – 11:45 PM PDTReplay Sept 23 7:15 – 7:45 AM PDTLive Ask the ExpertSept 22 7:30 – 8:00 PM PDTSept 23 11:30 – 12:00 PM PDT Ask the […]
This post is also available in: Deutsch Automatic scaling of metrics or measures is possible in Power BI for just about any visual. However, there is currently no possibility to dynamically adjust tables or the matrix. With this selection the values can be displayed in millions, for example. Even if the values are restricted due to a slicer, filter or drill down, the manually set scaling is kept. Depending on the constellation, this leads to the strange situation that no more information can be obtained from the report because the values are too low for scaling. While this is handled in a graphical visual and the scaling is adjusted, this option is unfortunately not available in a table or matrix. Dynamic scaling with DAX However, you can influence the formatting directly in a DAX measure so that it automatically adapts to the displayed value. The FORMATfunction will help us here. This function is similar to the formatting in Excel and only allows us to adjust the visualization, the values remain unchanged. The following syntax would display the value as a normal number: The documentation for custom formats shows us that a comma helps to “shrink” the displayed number in […]