close

How To Create A Table From Another Table In Power BI?

Posted by Errin O'Connor on May, 11, 2021 01:05

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

Method: ONE

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

Create Table from table in Power BI
  • 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:
Power BI table functions

     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

Method: TWO        

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.

Power BI Consulting

Method: THREE

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).

Table functions in Power BI

You can see in the above screenshot how to do it.

Winding Up!

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.

[gravityforms id=41 title=”true” description=”false”]
<div class='gf_browser_chrome gform_wrapper exit_intent_popup_wrapper gform_legacy_markup_wrapper' id='gform_wrapper_41' > <div class='gform_heading'> <h3 class="gform_title">Exit Intent</h3> <span class='gform_description'></span> </div><form method='post' enctype='multipart/form-data' id='gform_41' class='exit_intent_popup gform_legacy_markup' action='/how-to-create-a-table-from-another-table-in-power-bi/' > <div class='gform_body gform-body'><ul id='gform_fields_41' class='gform_fields top_label form_sublabel_below description_below'><li id="field_41_1" class="gfield gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_1' id='input_41_1' type='hidden' class='gform_hidden' aria-invalid="false" value='https://www.epcgroup.net/how-to-create-a-table-from-another-table-in-power-bi/' /></div></li><li id="field_41_9" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_9' >Full Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_9' id='input_41_9' type='text' value='' class='medium' placeholder='Full Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_6" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_6' >Email<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_email'> <input name='input_6' id='input_41_6' type='text' value='' class='medium' placeholder='Email Address' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_7" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_7' >Phone<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_phone'><input name='input_7' id='input_41_7' type='text' value='' class='medium' placeholder='Phone Number' aria-required="true" aria-invalid="false" /></div></li><li id="field_41_10" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_10' >Company Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_10' id='input_41_10' type='text' value='' class='medium' placeholder='Company Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_8" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_8' >Message<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_textarea'><textarea name='input_8' id='input_41_8' class='textarea medium' placeholder='Type your message here...' aria-required="true" aria-invalid="false" rows='10' cols='50'></textarea></div></li></ul></div> <div class='gform_footer top_label'> <input type='submit' id='gform_submit_button_41' class='gform_button button' value='Submit' onclick='if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; ' onkeypress='if( event.keyCode == 13 ){ if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; jQuery("#gform_41").trigger("submit",[true]); }' /> <input type='hidden' class='gform_hidden' name='is_submit_41' value='1' /> <input type='hidden' class='gform_hidden' name='gform_submit' value='41' /> <input type='hidden' class='gform_hidden' name='gform_unique_id' value='' /> <input type='hidden' class='gform_hidden' name='state_41' value='WyJbXSIsIjEwNTJhNGVmMWMyNzI3YTJmMjdiZTA1NjU4ZDMzYzY3Il0=' /> <input type='hidden' class='gform_hidden' name='gform_target_page_number_41' id='gform_target_page_number_41' value='0' /> <input type='hidden' class='gform_hidden' name='gform_source_page_number_41' id='gform_source_page_number_41' value='1' /> <input type='hidden' name='gform_field_values' value='' /> </div> </form> </div>