Forum Discussion
Anonymous5
Oct 06, 2019Copper Contributor
Merge Question Between Data Tables with Shared Column
Hello Everyone,
I am fairly new to Excel Power Query but I am really liking this tool and what it can do. I am currently using it to access an SQL database. I am familiar with how to Merge queries of two different tables around a shared column. My question is regarding something I haven't encountered up to now.
To be more specific my company uses a maintenance tracking program that is backed up with the SQL table. I can pretty easily access the table I need to get basic information about a particular job ("WO" for Work Order). There are a couple fields I am after though that involve written descriptions of the problem and work done to repair the issue. This is represented in the first table by a unique identifier. These terms are a 9-digit number and they are respectively called "Description_ID" and "Findings_ID".
To obtain the actual text data, I need to head over to a separate table called "WO_Remarks". It is a 2-column data table with a 9-digit number in the first column and the actual raw text in the second. As it turns out, this table has combined both "Description_ID" and "Findings_ID" into a single column all mixed together.
My question is, is there a way for Power Query to parse out the text entries for each of the columns in the first table. Ideally I'd like to merge them so that the larger and more descriptive "WO" table would include the "description" and "findings" text for reporting purposes.
Let me know if any of this is unclear and I'd be happy to provide some screen shots.
Rgds,
Rob
Rob, you may merge your first table with second one on WO_Description_ID and extract Remarks column. After that merge resulting table with second one on FINDINGS_ID and extract remarks as Remarks.1 column.
Select Remarks and Remarks.1 columns, on Power Query editor ribbon Add Column->Merge Columns, merge them with custom line feed separator #(lf). Or any one which you prefer. All remarks will be in resulting column.
Hi Rob,
Yes, it's better with screenshort or small sample which illustrates data logic. Like this
- Anonymous5Copper Contributor
Hi Sergei,
Thanks for the reply. To clarify things, please see a screenshot of the first table named "WO":
As you can see there are two columns "WO_Description_ID" and "Findings_ID". There are for the most part sequential but the same numbering scheme. Each row is associated with a unique work order.
The second table below is in the same database and titled "WO_Remarks":
This has the first column where the "description ID" and "findings ID" from above have all been collectively dumped in a single column. I am after the content of the "remarks" column above but need to extract this text using the uniqe "ID" identifier. Since it is done this way, every row of the first table will correspond to two rows of the second table (one row for description and one for findings remark text).
How can I extract this data from the second table and tie to the correct individual row of the first table?
Thank you,
Rob
Rob, you may merge your first table with second one on WO_Description_ID and extract Remarks column. After that merge resulting table with second one on FINDINGS_ID and extract remarks as Remarks.1 column.
Select Remarks and Remarks.1 columns, on Power Query editor ribbon Add Column->Merge Columns, merge them with custom line feed separator #(lf). Or any one which you prefer. All remarks will be in resulting column.