Forum Discussion

Anonymous5's avatar
Anonymous5
Copper Contributor
Oct 06, 2019

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

  • Anonymous5 

    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.

    • Anonymous5's avatar
      Anonymous5
      Copper 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

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Anonymous5 

        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.

Resources