Trending February 2024 # Static Tables In Power Query, Power Pivot And Power Bi # Suggested March 2024 # Top 6 Popular

You are reading the article Static Tables In Power Query, Power Pivot And Power Bi updated in February 2024 on the website Eastwest.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Static Tables In Power Query, Power Pivot And Power Bi

Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you’d write a query to load it from an external source.

But any time you have data that won’t change (or changes rarely), you can use a static table. That is, a table that doesn’t need a data source, it is created directly inside Power Query or the Data Model.

Watch the Video

Download Sample Files

Enter your email address below to download the sample file.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the files and code used in this post

For example, You might have a table which can be used to calculate bonus amounts when certain sales thresholds are met.

This Threshold column is the $ value of sales you need to reach in order to get a bonus which is calculated by multiplying that threshold value by the rate.

Such data is only used for reference or calculations so there’s really no need to load it from an external source.

In this post I’m going to show you 5 ways to create tables in Power Query, Power Pivot and Power BI without loading any data.

#table in Power Query

There are a number of ways to create a table in Power Query using functions like Table.FromList, Table.FromColumns, etc. but the way I find most flexible and use most often is the #table function.

In Excel open the Power Query editor and start a new blank query. Then open the Advanced Editor.

The syntax to create a table with #table is this

1 Start with the #table function name

2 Then declare that we’re creating a type table

3 The column names

4 The data types of the columns

5 The data for the columns

If you want more columns then declare more in Section 3. To add more rows just add more lines in Section 5.

Filling in the data needed to create the Bonus Rates table

Gives me this table

If at some point I do need to change a value in the table, just open the query and make the change.

Power Pivot

With Power Pivot it’s as easy as pasting in the data.

First, copy the data from the sheet

Then open Power Pivot

Because I’ve already copied data the Paste button is available

You can’t make any changes to this table so if you need to alter it, you’ll have to delete it and start over.

Close Power Pivot and back in Excel if I insert a pivot table

and select ‘Use this workbooks data model’

In the Pivot Table Fields you can see that the table is there ready to be used.

Power BI – Enter Data

In Power BI Desktop there’s a button on the Home tab of the Ribbon called Enter data.

Your browser does not support the video tag.

You can move around the table using the arrow (cursor) keys on your keyboard, or cick into cells with you mouse.

You can give your columns names, moving the cursor to the right adds columns, and moving it down adds rows.

If you make a mistake you can delete the columns and rows.

Your browser does not support the video tag.

The table is there as entered but you can see the data type of the columns is Any so they have to be changed.

If I open the Advanced Editor you’ll see that the table is Stored as an Encoded, Compressed JSON Document and can’t be edited.

DAX Table Constructor

The table constructor syntax is very simple

You start with the table name NewTable in this example image. The table contents is surrounded by curly braces {} where each line is a row in the table.

Each row of data is surrounded by parentheses/brackets and rows are separated by a comma.

The number of columns is determined by the number of values in each row.

To create the BonusRates table I use this

There isn’t any way to name the columns when constructing the table in this way.

By default if there is only 1 column the column name is Value. If there is more than 1 column they are called Value1, Value2 etc

The data type of the column is determined by the data you enter

You can see here that Threshold is whole number, and Rate will be decimal

If you have a column of mixed data types, the entire column is converted to a common data type.

If I change one of the values in the Threshold column to text, the entire column is now text.

One useful feature of the table constructor is that the values in the table can be the result of any DAX expression that returns a scalar value.

You can refer to measures, or columns in other tables and use those to construct the table.

DATATABLE Function in DAX

The syntax for DATATABLE is similar to #table in Power Query

1 Give the new table a name

2 Call the DATATABLE function

3 The column names

4 The data types of the columns

5 The data for the columns

The columns are already named and the columns have a data type, so that saves us a few steps compared to using the table constructor.

Conclusion

Static tables can be handy when you don’t want to load data that never (or rarely) changes.

I’ve shown you 5 different ways to create static tables in Power Query and DAX. I hope these approaches are useful to you.

You're reading Static Tables In Power Query, Power Pivot And Power Bi

Unpivot And Pivot Basics In Power Bi – Query Editor Review

There are many occasions where you may bring data into Power BI and the data structure is not optimized for your data model. In this article, I’m going to work through when you should utilize the unpivot or pivot feature in the Query Editor within your data queries.

One thing to bear in mind early on when working on data inside of Power BI is you generally want it to be in an unpivoted format. This is the most optimized way a table can be set up for all of the DAX calculation logic that you will run over your raw data once it is integrated into your model inside of Power BI.

So you need to do a lot of work here in the Query Editor.

What we have here is an example of a table which is pivoted:

This contains raw data that is being brought into the query editor of Power BI. It is currently in a pivoted format where we have the dates across the top.

What we need here is for these dates to be unpivoted so all of the values, which are of a similar nature, are in one column. This way we can run simple aggregations like SUM or iterations like SUMX over these particular values.

This setup is the most optimized way that you can set up your tables in Power BI.

It is something that I really recommend you look to do on any occasion where you see pivoted data.

You’ll see now that all of the values and the dates are retained in one column each. Then all you need to do is to change the particular column names to something more appropriate and intuitive.

You can also unpivot columns by going to the Transform tab of the Query Editor ribbon and then select Unpivot Columns.

If there is a rare occurrence where you want to pivot your data, then you can also do this quite easily in Power BI.

I’ll also show you below how you can pivot your data straight from the Query Editor ribbon.

I want to pivot the Attribute column, so I’m going to select the Attribute column first and then press Pivot Column in the ribbon.

Once I do that, a pop-up box will appear and I can select the values that I want to see within each new column. I’m selecting the column Value because I want to retain all of the amounts from my data in each new created column.

You will see now that it has pivoted my data back to the way it was. In theory, you wouldn’t have to actually do this because the data is already set up like that.

Just as an example here, I want to showcase how you can also pivot your raw data if this is the structure you need.

As mentioned earlier, you generally (95% of the time) want your data in an unpivoted format.

There may be some occasions where you might need to do a pivot as an intermediary step in your data transformation in the Query Editor, but from my experience, this is relatively rare.

This is a great technique and it is one that I use very frequently just because of the messiness of a lot of data in its rawest form.

Enjoy reviewing this one.

Sam

Excel Power Pivot Charts

Excel Power Pivot Charts – Creation

A PivotChart based on Data Model and created from the Power Pivot window is a Power PivotChart. Though it has some features similar to Excel PivotChart, there are other features that make it more powerful.

In this chapter, you will learn about Power PivotCharts. Henceforth we refer to them as PivotCharts, for simplicity.

Creating a PivotChart

Suppose you want to create a PivotChart based on the following Data Model.

Select PivotChart from the dropdown list.

An empty PivotChart is created on a new worksheet in the Excel window.

As you can observe, all the tables in the data model are displayed in the PivotChart Fields list.

Drag the fields − Salesperson and Region to AXIS area.

Two field buttons for the two selected fields appear on the PivotChart. These are the Axis field buttons. The use of field buttons is to filter data that is displayed on the PivotChart.

Drag TotalSalesAmount from each of the four tables– East_Sales, North_Sales, South_Sales and West_Sales to ∑ VALUES area.

The following appear on the worksheet −

In the PivotChart, column chart is displayed by default.

In the LEGEND area, ∑ VALUES are added.

The Values appear in the Legend in the PivotChart, with title Values.

The Value Field Buttons appear on the PivotChart. You can remove the legend and the value field buttons for a tidier look of the PivotChart.

Uncheck the box Legend in the Chart Elements list. The Legend is removed from the PivotChart.

Select Hide Value Field Buttons on Chart from the dropdown list.

The value field buttons on the chart are removed.

Note − The display of field buttons and/or legend depends on the context of the PivotChart. You need to decide what is required to be displayed.

PivotChart Fields List

As in the case of Power PivotTable, Power PivotChart Fields list also contains two tabs – ACTIVE and ALL. Under the ALL tab, all the data tables in the Power Pivot Data Model are displayed. Under the ACTIVE tab, the tables from which the fields are added to PivotChart are displayed.

Likewise, the areas are as in the case of Excel PivotChart. There four areas are −

AXIS (Categories)

LEGEND (Series)

∑ VALUES

FILTERS

As you have seen in the previous section, Legend is populated with ∑ Values. Further, field buttons are added to the PivotChart for the ease of filtering the data that is being displayed.

Filters in PivotChart

The dropdown list that appears looks as follows −

You can select the values that you want to display. Alternatively, you can place the field in FILTERS area for filtering the values.

Drag the field Region to FILTERS area. The Report Filter button – Region appears on the PivotChart.

You can select the values that you want to display.

Slicers in PivotChart

Using Slicers is another option to filter data in the Power PivotChart.

All the tables and the corresponding fields appear in the Insert Slicer dialog box.

Slicer for the field Region appears on the worksheet.

Remember that you are able to do all these in a few minutes and also dynamically because of the Power Pivot Data Model and defined relationships.

PivotChart Tools

In Power PivotChart, the PIVOTCHART TOOLS has three tabs on the Ribbon as against two tabs in Excel PivotChart −

ANALYZE

DESIGN

FORMAT

The third tab − FORMAT is the additional tab in Power PivotChart.

The options on the Ribbon under FORMAT tab are all for adding splendor to your PivotChart. You can use these options judiciously, without getting over bored.

Advertisements

Getpivotdata Function For Power Pivot

The GETPIVOTDATA function divides Excel users. You either love it or hate it, but there are good reasons for learning to love it. I wrote about using the GETPIVOTDATA function for regular PivotTables many years ago and hopefully you’re embracing it now. If you’re a Power Pivot user, then you may have found that the GETPIVOTDATA function for Power Pivot, aka the Data Model, works a little differently.

Watch the Video

Download the Workbook

Download the Excel Workbook . Note: this is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Regular vs Power Pivot GETPIVOTDATA Function

You can easily tell if you’re referencing a regular PivotTable vs a Power Pivot PivotTable because the GETPIVOTDATA function for Power Pivot will have ‘measures’ in the formula arguments, as you can see below:

=GETPIVOTDATA("[

Measures

].[Average of Order Amount]",$B$9, "[Dates].[MonthName]","[Dates].[MonthName].&[Jan]")

Whereas a regular PivotTable GETPIVOTDATA formula won’t:

=GETPIVOTDATA("Order Amount",$A$4,"Order Date",1,"Years",2009)

Notice the Power Pivot GETPIVOTDATA function also references the table name, field name and item name:

For example:

The Problem with GETPIVOTDATA

GETPIVOTDATA is not relative, meaning if you copy and paste it to another cell it will give you the same results, as you can see below with the formula in cell D11 that simply returns the value for Alberta from cell C11. When copied down to row 23 it returns the same value for Alberta on every row:

This is because the province, Alberta, is hard-keyed in the formula:

=GETPIVOTDATA("[Measures].[Average of Order Amount]",$B$10,"[Customers].[Province]","[Customers].[Province].&[

Alberta

]")

Relative GETPIVOTDATA Function for Power Pivot

Don’t despair because writing relative Power Pivot GETPIVOTDATA formulas is easy. All we need to do is replace the hard-keyed argument with a reference to the cell containing the province, so that when it’s copied down it picks up the next province.

Using the formula above we replace Alberta with a reference to cell B11, as shown below:

=GETPIVOTDATA("[Measures].[Average of Order Amount]",$B$10,"[Customers].[Province]","[Customers].[Province].&["&

B11

&"]")

The tricky part is getting the square brackets and double quotes correct so I’ve highlighted the extra characters required in blue font below:

=GETPIVOTDATA("[Measures].[Average of Order Amount]",$B$10,"[Customers].[Province]","[Customers].[Province].&[

"&

B11

&"]

")

These characters in blue simply concatenate a closing square bracket to the province name. When we copy this formula down, we get relative references that return the Order Amount for the corresponding row:

While the above example handles a single relative reference, we can also make multiple row and column labels relative. In the example PivotTable below, we have both province and month:

The GETPIVOTDATA formula referencing cell C11 would be:

=GETPIVOTDATA("[Measures].[Average of Order Amount]",$B$9,"[Customers].[Province]", "[Customers].[Province].&[

Alberta

]","[Dates].[MonthName]","[Dates].[MonthName].&[

Jan

]")

We can change the province and month values in red font to make the formula relative like so:

=GETPIVOTDATA("[Measures].[Average of Order Amount]",$B$9,"[Customers].[Province]", "[Customers].[Province].&[

"&B11&"]"

,"[Dates].[MonthName]","[Dates].[MonthName].&[

"&C10&"]"

)

Note: In the examples above I am referencing the PivotTable row and column labels, but you can also reference cells on other sheets that contain the province and month names. You just need to ensure the spelling is the same as that found in the PivotTable.

Tip: The GETPIVOTDATA function arguments are:

=GETPIVOTDATA(data_field, pivot_table, field, item,...)

Since we can only replace ‘item’ arguments with cell references, I always let Excel write the GETPIVOTDATA formula first by referencing a PivotTable value cell that I want returned. I then edit the formula and replace the hard-keyed item arguments with cell references.

So, now you have no excuse not to use GETPIVOTDATA. I encourage you to use it because it reduces the risk of formula errors caused by changes to the shape of the PivotTable.

Dynamic GETPIVOTDATA Formulas

The example above deals with copying and pasting GETPIVOTDATA formulas, but sometimes you might want to link it to a drop-down list that allows your user to choose what data they want displayed, as shown below:

Related Lessons

Relative GETPIVOTDATA formulas for regular PivotTables.

Power Bi Designs For Compelling Reports

In this blog, I want to give you a sneak peek into something new that we’ve been working on at Enterprise DNA. I’m going to introduce to you some Power BI designs that we’ve developed to help you create compelling Power BI reports. You can watch the full video of this tutorial at the bottom of this blog.

The Team is all about empowering Power BI users as they hone their skills and expertise in Power BI. One of the most important skills that we believe has a huge demand is the ability to display insights effectively and compellingly. This upcoming initiative that we call the “Power BI Design Kits” will try to bridge that gap between data and design.

Due to the skills of our Enterprise DNA experts and their participation in the Power BI challenges, we’re seeing some incredible designs of Power BI reports. They are probably some of the best designs out there by far. So, we thought why can’t we create design kits that others can use within their own Power BI reports?

Instead of having to build from scratch, we support you in developing reports about five times quicker than if you were to recreate them. Furthermore, you’ll have some flexibility in how you can rework the design and present it in a more customized way, such as changing colors.

Here’s an example of what our Power BI design kits look like.

The idea is that we can build this inside PowerPoint or a tool like Canva. Doing this enables you to use this preset template, adjust a few colors, etc. If you want, you can change a few things. For example, you could delete things. Also, you could come in here and create different pages, just like you would inside a Power BI report. First, you create these pages here before you move into Power BI.

If you try to build this directly inside Power BI, you’ll find a lot of elements there. And if you can get this into just one wallpaper or background for your Power BI report, it actually makes the performance of your report that much better and faster. Also, you don’t have all these individual elements that you’re trying to move around; it’s literally just one background.

After you make the necessary changes, you can save it. When you come into Power BI, instead of recreating that from scratch using shapes and icons, you can just embed it in your background. Go to Page background, then Add image and make your desired changes.

For example, you wanted to create a visualization. You can simply create it on top of this background.

These are just some of the examples of what you can get from our Power BI Design Kits, but there’s more. These are the things we’re trying to solve for those who are using our platform. Our goal is to make your lives easier when working with Power BI reports.

I hope this gives you a lot of excitement. I’m really looking forward to showing you more details in the near future. Watch out for a lot more coming up on our platform very soon.

Cheers!

Sam

Power Bi Custom Sort Using Dax

Many people believe that we cannot perform Power BI custom sort using DAX, but that is not true. DAX can absolutely guarantee sort, and it’s actually pretty simple to do. In today’s blog, I will show you how you can easily do exactly that. You can watch the full video of this tutorial at the bottom of this blog.

Let’s start with a simple data model. Our data table has three Campaigns, Analyzed ROI, and Cost. 

As you can see in the table, they have the same costs but their ROIs are different. The concept here is to know which campaigns we should run depending on their ROIs for a given budget. 

Let’s start with a measure with a simple while loop. 

We’ll set our budget at 20000 and save it in the Budget variable using VAR_Budget.

And then we’ll use the while loop by creating a Table variable VAR_Table. 

We then use ADDCOLUMNS that go against our base Table 4 and add a running total column using “_RT”.

Next, we grab the [Analyzed ROI] in our current row saved as VAR_ROI. Then, we take our Budget. From it, we subtract SUMX, filter Table 4 for the Analyzed ROI that is greater than or equal to the ROI that we grab for our current row, and grab the [Cost]. 

These lines will take the highest ROI in the first row, then the next highest for the next row, and so on. So essentially, we’ll have a column that’s a running total counting down to zero in negatives.

Then we exit our while loop. We create our FinalTable variable with VAR_FinalTable where we simply filter our Table variable for the Running Total that is greater than or equal to zero. We apply this filter because anything below zero means we’ve gone negative and we’re out of budget.

Then we can use CONCATENATEX for those values. We’ll include our columns—Campaigns, Analyzed ROI, and Cost—and add UNICHAR.

We can unhide this Card visual in the Selection pane and see that we have our Instagram and SEM, which are our two highest ROIs.

Let’s check our original table to compare the results.

We can see that the PPC indeed has a lower ROI than SEM and Instagram. 

However, as you may have noticed, our result didn’t come back in the correct order, with Instagram’s 8.1 showing first before SEM’s 8.15. 

But what if we want Instagram to be returned first because it has the highest ROI and SEM to be returned second? Basically, we want to guarantee sort order. This gets a little bit tricky, but it’s not difficult to understand how to do it.

Here’s how our function should look like, with the bottom section being the same as the while loop we had earlier.

And in order to guarantee sort order, we need to do a little extra work. 

Again, we start with our VAR_Budget which we set at 20000. Then, we create our Count variable using VAR_CountI and apply COUNTROWS on Table 4. 

In the next line, we will use the CONCATENATEX function to concatenate our Campaigns, Analyzed ROI, and Cost columns. You can use anything as a separator as long as it’s not in your data, so in this case, let’s just use asterisks. 

This example is interesting because we get a for loop, while loop, and all kinds of cool functions. This time around, we’ll use the for loop. 

For this example, Table1 is our for loop, and what we’re doing is ADDCOLUMNS to GENERATESERIES that goes from one to our count by one written as (1,_Count,1).

In our Row, we then use PATHITEM because we use the pipe character in order to separate the rows of our table. Using PATHITEM, we then grab the path from the Ordered variable, which is our string. 

We also use Value, which is our automatic column that is created with GENERATESERIES, or our Calendars 1, 2, 3, 4, 5, until we exhaust the number of rows we have in our table. Then, we return it back as Text. 

What we have now is a guaranteed sort order for our table, which goes from the highest ROI to the lowest. So, here’s your DAX for guaranteeing sort order. 

Now, in order to get this in a usable format, we have to do Table2 where we’ll pick out the individual columns from our string that we have.

Let’s use another ADDCOLUMNS to add to Table1, our for loop. For Campaigns, create a campaigns column and use SUBSTITUTE to replace asterisks with pipes. Then, we’ll use PATHITEM on RowPath to grab the first element and return it as TEXT.

In the Analyzed ROI column, we’ll basically do the same thing. SUBSTITUTE asterisks with pipes. Then, grab the second item in our path and return it as TEXT. We do this because for some reason, PATHITEM lets us return as integer or text but not decimal numbers.

That’s kind of weird, but we can solve that because all we have to do is add zero, and DAX will automatically convert the TEXT string to a numeric value. 

Again, we do the same thing for the Cost column. We use SUBSTITUTE and PATHITEM to grab the third item. Then, to return a number, we add zero again because it’s a whole number in this case.

The rest of our code is the while loop that we discussed earlier. What we get now is our guaranteed sort order from highest to lowest campaign. 

Our SEM was our highest ranked ROI for our campaign and Instagram is the second one. 

Using the while and for loops, we successfully used Power BI custom sort in our data model based on their ROIs—a proof that DAX can definitely guarantee sort order. 

It is important to ensure that your data is correctly sorted in DAX to accurately analyze and visualize it. You can apply what you learned in this tutorial to appropriately sort your data for your next report.

All the best,

Greg Deckler

Update the detailed information about Static Tables In Power Query, Power Pivot And Power Bi on the Eastwest.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!