Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors