cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New2PowerBI
Helper III
Helper III

How to Join Two Columns Into One Based on Data Values

I have a dilemma in my Tables and Columns. We manage preventative work in an application and I'm working with that data. I notice that when the preventative work is tied to an "Asset", the "Location" column in the Asset Table is populated, however, if the preventative work is not tied to an "Asset", the "Location" column in the Asset Table is NOT populated, but IT IS populated in a "Location" column, from the Preventative Maintenance (PM) Table. Here is a visual representation of what I see:

 

LOCATIONLOCATIONPMNUMROUTEDESCRIPTIONASSETNUMCSTM_IMPLEMENTERFREQUENCYFREQUNITNEXTDATE
DIV.POOL.VEHC.01 STX65642 VEHICLE STATE REGISTRATION / INSPECTION - DIV - ANNUALVEHC19992SHIROCKW1YEARS3/18/2018 0:00
PS.SUN.TANK.01.LS.01 STX22701 BREAKOUT TANKS - OVERFILL PROTECTION - SUN - ANNUALLS20739FONTESD1YEARS3/17/2018 0:00
PS.SUN.TANK.02.LS.01 STX22702 BREAKOUT TANKS - OVERFILL PROTECTION - SUN - ANNUALLS20740FONTESD1YEARS3/17/2018 0:00
PS.SUN.PT.02 STX23211 OPSD INSP - ELECTRICAL - SUN - ANNUALPT66735FONTESD1YEARS3/17/2018 0:00
PS.SUN.UPS.01 STX68897 UPS ELECTRICAL INSP - SUN - ANNUALUPS28764FONTESD1YEARS3/17/2018 0:00
PS.SUN.HS.01 STX69913 STATION PROTECTIVE DEVICE - SUN - ANNUALHS19769FONTESD1YEARS3/17/2018 0:00
PS.SUN.PT.01 STX76853 OPSD INSP - ELECTRICAL - SUN - ANNUALPT25924FONTESD1YEARS3/17/2018 0:00
PS.SUN.PT.03 STX76854 OPSD INSP - ELECTRICAL - SUN - ANNUALPT25925FONTESD1YEARS3/17/2018 0:00
 PS.SUNSTX78624 ANNUAL ELECTRICAL INSPECTION - SUNFIELD PUMP STATION FONTESD1YEARS3/17/2018 0:00
 PS.SUNSTX79539STX2086LEVEL MEASUREMENT - SUN - ANNUAL FONTESD1YEARS3/17/2018 0:00
 INJ.PNNBSTX78662 ANNUAL ELECTRICAL INSPECTION - PATTON NORTH BATTERY OSORIOM1YEARS3/16/2018 0:00

 

I want to build a "cleaner" report...the end user doesn't understand how this works and essentially wants just "one location" value.  What they would like to do is see the report and essentially show everything at "PS.SUN" and get a list of that information displayed.  They don't really care if tied to an asset or not, it's all work at that "main site".  How do I get this to work?  Totally lost here; any help will be greatly appreciated. 

1 ACCEPTED SOLUTION

@New2PowerBI

 

Use a calculated Column in Preventative Maintenance Table. 

 

related.png




Lima - Peru

View solution in original post

9 REPLIES 9
MarcelBeug
Community Champion
Community Champion

In the Query Editor you can simply choose Merge Columns on the Transform tab.

Specializing in Power Query Formula Language (M)

I cannot click on that option; is there a step prior to that?

Watch this video.

Specializing in Power Query Formula Language (M)

Thanks; saw the video but cannot select both columns, they are in different tables and when I try and use Shift and/or Control with a Click Select, one of the selections defaults to the first column of the last table, so it merges a different column and not the one I want.  Is there another way to select them?

I also went to each table and moved the column of Location to the beginning to see if that would make the selection easier but it didn't.

@New2PowerBI

 

What it the relationship between both tables (Asset and Preventative Maintenance)

 

I think you can use Related Function.




Lima - Peru

Asset Num is in common for both, one Asset Num to many on the Preventative Maintenance Table. 

 

How can I use the Related Function?

@New2PowerBI

 

Use a calculated Column in Preventative Maintenance Table. 

 

related.png




Lima - Peru

@MarcelBeug and @Vvelarde

 

Thank you to both!  I used the Related option to work this one, but the Merge Column will come in handy on other things I'm working on.  Thanks again!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.