Skip to main content

Data Entry Options in Power BI

Where do you go when you need to create a simple support table in Power BI? I usually prefer the Power Query Editor and use 'Enter Data' option. But what other options do we have? Would you like to find out? Then you've come to the right place!

Other than Power Query Editor, we also have options in DAX to create a support table, and I am going to look into them one by one. Let's say I want to create a support table for color codes that I use in my report. First I am going to do it with Power Query Editor.


After clicking 'Enter Data' at Power Query Editor, we get a new window where we can edit cells like in Excel. I use this option most of the time because it's easy to do some changes later, from source settings. So how do we do the same thing in DAX? To have exactly the same table by using DAX, we need to click on 'New Table' on Power BI Desktop and write the formula below: 


Well, it's not "exactly the same". Power BI gave default column names by itself because with this method there is no room for column names in the formula. But they can be changed by clicking on default names. The important detail to remember, you need to have the same structure for every row, otherwise, it creates an error. What makes this option better than entering data at Query Editor is you can write DAX expressions within this table.

    

The other option to create the same table requires using ROW and UNION function together. This one also allows users to write functions and we are able to name our columns within the formula. If you have a long list of columns and don't want to change them later, this option might be preferable. The important thing to remember while using UNION is, the table should have the same number of columns and they should be in the same order.

The last function that I'd like to mention is called DATATABLE:

DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2..., 
                            {{Value1, Value2...}, {ValueN, ValueN+1...}...})

The beauty of the DATATABLE function is: it not only lets you name the columns but also allows you to choose the data type for your columns. This is not an option for the methods I have mentioned above. Integer, Double, String, Boolean, Currency, and Datetime are the data types that can be chosen. By the way, Double stands for decimal numbers. Other than that, missing values are treated identically to BLANK().

The downside of this approach is using DAX functions with it isn't possible. So which one is the best? You name it if you were able to decide because I wasn't. When I need a support table next time, my choice will be depended upon whether I need a DAX formula within the table or not. Like it happens most of the time, the best approach depends on the situation.
     
Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...
Post a Comment
Read more

Customizing Themes With Advanced Elements in Power BI

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...
Post a Comment
Read more