topic Re: How to Join Two Columns Into One Based on Data Values in Desktop

How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 14:29:57 GMT

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:

LOCATION	LOCATION	PMNUM	ROUTE	DESCRIPTION	ASSETNUM	CSTM_IMPLEMENTER	FREQUENCY	FREQUNIT	NEXTDATE
DIV.POOL.VEHC.01		STX65642		VEHICLE STATE REGISTRATION / INSPECTION - DIV - ANNUAL	VEHC19992	SHIROCKW	1	YEARS	3/18/2018 0:00
PS.SUN.TANK.01.LS.01		STX22701		BREAKOUT TANKS - OVERFILL PROTECTION - SUN - ANNUAL	LS20739	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.TANK.02.LS.01		STX22702		BREAKOUT TANKS - OVERFILL PROTECTION - SUN - ANNUAL	LS20740	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.PT.02		STX23211		OPSD INSP - ELECTRICAL - SUN - ANNUAL	PT66735	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.UPS.01		STX68897		UPS ELECTRICAL INSP - SUN - ANNUAL	UPS28764	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.HS.01		STX69913		STATION PROTECTIVE DEVICE - SUN - ANNUAL	HS19769	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.PT.01		STX76853		OPSD INSP - ELECTRICAL - SUN - ANNUAL	PT25924	FONTESD	1	YEARS	3/17/2018 0:00
PS.SUN.PT.03		STX76854		OPSD INSP - ELECTRICAL - SUN - ANNUAL	PT25925	FONTESD	1	YEARS	3/17/2018 0:00
	PS.SUN	STX78624		ANNUAL ELECTRICAL INSPECTION - SUNFIELD PUMP STATION		FONTESD	1	YEARS	3/17/2018 0:00
	PS.SUN	STX79539	STX2086	LEVEL MEASUREMENT - SUN - ANNUAL		FONTESD	1	YEARS	3/17/2018 0:00
	INJ.PNNB	STX78662		ANNUAL ELECTRICAL INSPECTION - PATTON NORTH BATTERY		OSORIOM	1	YEARS	3/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.

Re: How to Join Two Columns Into One Based on Data Values

MarcelBeug — Tue, 21 Mar 2017 19:47:51 GMT

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

Re: How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 20:10:18 GMT

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

Re: How to Join Two Columns Into One Based on Data Values

MarcelBeug — Tue, 21 Mar 2017 20:29:01 GMT

Watch this video.

Re: How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 21:11:24 GMT

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?

Re: How to Join Two Columns Into One Based on Data Values

Vvelarde — Tue, 21 Mar 2017 21:15:16 GMT

New2PowerBI

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

I think you can use Related Function.

Re: How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 21:15:44 GMT

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.

Re: How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 21:16:55 GMT

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

How can I use the Related Function?

Re: How to Join Two Columns Into One Based on Data Values

Vvelarde — Tue, 21 Mar 2017 21:20:38 GMT

New2PowerBI

Use a calculated Column in Preventative Maintenance Table.

Re: How to Join Two Columns Into One Based on Data Values

New2PowerBI — Tue, 21 Mar 2017 21:45:49 GMT

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!