Forum Discussion

Sarcasticus's avatar
Sarcasticus
Frequent Visitor
3 years ago

Dynamically merge two tables repeatedly

Using PowerBI Datamarts to build a diet Data Warehouse for a third-party solution. The solution will export Excel files to a data lake via SFTP that are essentially one per table. The way this third ...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi Sarcasticus ,

According to your description, I create a sample and please see if this can help you:

1.In Claim Table, select CustomCode1ID, CustomCode2ID, CustomCode3ID at the same time then click Unpivot Columns.

2.Select Claim Table, then click Merge Queries as New.

3.In the merged new column, expand Description and CodeTypeID columns.

4.Select the new table, click Merge Queries

5.Expand the Description column.

Get the result:

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Sarcasticus's avatar
    Sarcasticus
    Frequent Visitor
    3 years ago

    Thank you.

     

    Just as an FYI, from there, I pivoted on the CodeTypeTable.Description with the value field being the CodeType.Description, giving me what I needed.

  • Sarcasticus's avatar
    Sarcasticus
    Frequent Visitor
    3 years ago

    Well, hmmm. Unfortunately, this isn't quite as easy as I'd hoped. I note that I'm missing a lot of rows. In fact, the issue is that I have a number of items in the Claims table that don't have any of those CustomCode columns mapped, meaning they're not showing up when I unpivot. In other words, I have rows in my overly simplified version with something like the following:

     

    ClaimIDCustomCode1IDCustomCode2IDCustomCode3ID
    1100391012110189
    2100391012210189
    3100401012210189
    410040[null]10190
    5[null]1012110190
    6[null][null]10190
    71004010121[null]
    8[null][null][null]

    (Please note there are other columns in the table, just not pertinent to the CodeTable issue.

     

    In addition, I then have to pivot, unpivot, remove duplicates, and pivot again, which is, well, awful (for performance, as well as for a clean, repeatable process).

     

    Every query I run is going to have something like this coming in from my 3rd party ERP platform.

     

    I'm thinking just a series of manual merges against the CodeTable, followed by a manual rename. The custom codes don't change that much, and when they do, it's usually just an entry in the CodeTable (change a status here or there, something like that).