Dynamically merge two tables repeatedly

[Sarcasticus]

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 party supports custom "lookup" fields (the platform calls it "Code Tables" even though there's only one all mashed together) involves something like this:

Claim Table

ClaimID	CustomCode1ID	CustomCode2ID	CustomCode3ID
1	10039	10121	10189
2	10039	10122	10189
3	10040	10122	10189
4	10040	[null]	10190
5	[null]	10121	10190
6	[null]	[null]	10190
7	10040	10121	[null]

CodeTable:

CodeID	Description	CodeTypeID
10039	Auto	1
10040	Property	1
10041	Life	1
10121	Paid	2
10122	Denied	2
10189	Lincoln	3
10190	Franklin	3

CodeTypeTable:

CodeTypeID	Description
1	Claim Type
2	Status
3	County

Now multiply the number of custom codes by a large number for each table (Claim, Policy, etc.). Ideally, I'd find a way to easily dynamically rename the column in addition to replacing the numeric value with the code table text. However, the problem is doing this over multiple merges starts to make my M code look disgusting and makes it hard to maintain

Any ideas?

[v-yanjiang-msft]

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]

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:

ClaimID	CustomCode1ID	CustomCode2ID	CustomCode3ID
1	10039	10121	10189
2	10039	10122	10189
3	10040	10122	10189
4	10040	[null]	10190
5	[null]	10121	10190
6	[null]	[null]	10190
7	10040	10121	[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).

[Sarcasticus]

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]

Bump (I got flagged for spam but thankfully got taken out of post jail).

Reading through other posts on the forum, thought I would provide some additional context:

New(ish) PowerQuery/PowerBI user. Familiar with data warehousing concepts but new to doing it in PowerBI/Fabric/Synapse/whatever MS is calling it this 5-minutes.

I'm very aware that the above is some combination of stupid question/repeatedly asked question in the forum, but I have spent some time searching and looking at related posts, and I'm seeing suggestions that are close, but not precisely, what I'm looking for (the answers seem to go off on a tangent compared to what I think I need.

I also don't know what will be the best option for performance:

Merge, Unpivot/pivot (tried this and boy was that going to look ugly), List.Accumulate (still wasn't clear on how that was going to work), or even doing it as DAX in a measure.

Basically, my very atrophied data warehousing muscles told me it's best to denormalize all of this into the main fact tables at data load, but I'm struggling to figure out how to do it for our use-case.

Thanks again, hopefully my overly simplified tables above speak for themselves in a simplified way.