How To Create A Table From Another Table In Power BI?
In this, we will be looking at how you can create a table from another table in Power Bi. As we already know that Power BI is a smart business intelligence tool and it has many features. You can either completely clone a Table or make a subset of it by using a powerful DAX Query. But Before we dive into the how-to part, let us investigate in what cases we might need to create a table with the help of another table.
Why Does One Need To Create A Table From Another Table In Power BI?
There can be multiple reasons to create a table from another table. One of them can be to simply merge the columns from multiple tables to create a larger schema. This is generally observed when a report or analysis needs to be created. In other cases, we pick specific columns from tables and create a new one. Sometimes, we do some mathematical calculations to like averaging, summation and put them in a new schema. To achieve all this, we can use a DAX or Data Analysis Expression which comes bundled with the Power BI software. It is very easy to learn and work with. Using formulas and expressions gives you the power to manipulate the data columns in a powerful and effective way.
DAX stands for Data Analysis Expressions. It is an inbuilt library of functions & operators which you can use to make formulas and expressions. This same expression can be used in data Analysis Services, Power Pivot in Excel data models too.
How To Create A Table From Another Table In Power BI
Please do as per directed to generate a table with the help from another table. We will make use of a calculated table to achieve this. A calculated table is a calculated data object created by a DAX query or expression which is derived from a part or whole of the table. We can also use more than one table to make a calculated table but there must be at least one existing one.
First click on the “New Table” option which you can find under “Modelling” tab
- A Power BI DAX formula bar will open where you can write and execute DAX expression
- Assume the previous table, named Sales had 3 columns: Order Id, Order_No, Order_Date
- We will put in the following DAX Expression:
Item = SUMMARIZE (Sales, Sales[item_id], Sales[Item_No])
You can also make use of SELECT COLUMN function to make a subset instead of grouping them
- SELECTCOLUMNS(Sales, “Id and No”, [item_id]&”, “&[item_no])
- Execute the above query and you will have another Table summarized columns designed by the name parameters
Alternatively, if you can simply duplicate an existing Table by copy-pasting it. Right-click > click on copy. Right-click on the blank areas below the table and click on paste. Viola! You have just duplicated your existing table.
Using Reference: You can duplicate an existing table by using the “reference feature”. The correct way to do it is too right to click on a data table and choose the “Reference” option (refer to the screenshot attached below).
You can see in the above screenshot how to do it.
You can take other paths to the same thing, as well, keeping in mind your requirements. Here we have used SUMMARIZE and SELECT COLUMN DAX queries to create a subset of an existing table. To duplicate a whole Table, we have used the copy feature to directly copy the whole schema and content of a relation.
You can also reach out to EPCGroup for Power BI Consulting. EPCGroup has more than 70+ in-house Power BI experts. We have helped numerous organizations with a transition to Power BI as SAAS BI software.