Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
3 years ago
Solved

Combine 2 colums into 1

Hi, 

I have one table (apps) which has app_id and app_status

 

Within this table, some app_id have a blank apps_status.

 

However, my 2nd table (app_lookup) has all the app_id that are missing an app_status in the apps table. 

 

I have joined the 2 tables on app_id. 

 

What I want is the values from the app_lookup table to fill in the blanks on the app_status within the apps table. 

 

Any idea how to do this please? 

Need Help
  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    M_SBS_6- Check this out.

    App Status Filled = 
IF(
    ISBLANK('apps'[app_status]),
    RELATED('app_lookup'[app_status]),
    'apps'[app_status]
)

3 Replies

  • Manoj_Nair's avatar
    Manoj_Nair
    Icon for Solution Supplier rankSolution Supplier
    3 years ago

    M_SBS_6- Check this out.

    App Status Filled = 
IF(
    ISBLANK('apps'[app_status]),
    RELATED('app_lookup'[app_status]),
    'apps'[app_status]
)
  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi, M_SBS_6 

     

    you can achieve the same result by performing a Merge operation and then expanding the values from the lookup table to fill in the blanks in the original table. Here's how you can do it:

    Step 1: Merge the tables

    1. In Power BI, go to the "Home" tab.
    2. Click on "Manage Relationships."
    3. Ensure there is a relationship between the "app_id" columns in both tables. If not, create a relationship between the "app_id" columns of the "apps" and "app_lookup" tables.

    Step 2: Perform the Merge

    1. Go back to the "Home" tab.
    2. Click on "Transform Data" to open Power Query Editor.
    3. In Power Query Editor, select the "apps" table.
    4. Click on the "Merge Queries" dropdown in the "Home" tab.
    5. Choose "Merge Queries as New" from the dropdown menu.
    6. In the "Merge" dialog box, select the "app_id" column from the "apps" table as the key column, and select the "app_id" column from the "app_lookup" table as the related column.
    7. Choose "Left Outer" as the join kind.
    8. Click "OK" to perform the merge.

    Step 3: Expand the values from the lookup table

    1. After the merge, you'll see a new column in the "apps" table containing a table with the data from the "app_lookup" table.
    2. Click on the double-arrow icon on the new column header to expand the values from the lookup table.
    3. In the "Expand" dialog box, select the columns you want to fill in the blanks (e.g., "app_status").
    4. Click "OK" to apply the expansion.

    Step 4: Remove unnecessary columns You can remove the merged column from Step 2 and any other columns that you no longer need for your analysis.

    Step 5: Load the data After making all the necessary transformations, click "Close & Apply" to load the data back into Power BI. The blanks in the "app_status" column in the "apps" table should now be filled with the values from the "app_lookup" table.

    Please note that these steps are specific to Power BI and may vary slightly depending on the version of Power BI you are using