Best practice in MSM: Usage of mapping tables

(5) Share

Report

Posted on by Lea Kraus

Hi team,

I'm looking for some best practices regarding MSM customizations for the following case:

For most of our activity data we don't have the columns "organizational unit" or "facility" (which are mandatory fields most of the time). But for all activity data we have another column called "Key" and we also a kind of mapping table where the "Key" column is mapped to "organizational unit" and "facility". So for all data imports I would need to use this mapping table to find out the columns "organizational unit" and "facility". And for most of the data imports we use Power Query.

What's the best practice to implement that in MSM?

My idea is to use the mapping table in each Power Query pipeline to do the mapping. But is there a better or more efficient way?

Thanks in advance!

Cheers,

Lea

Categories:

Sustainability Manager

All responses (3)

Answers (1)

Lea Kraus 56 on at

Like (1)

Report

Best practice in MSM: Usage of mapping tables

So we ended up using the approach as described - connecting to the table within PQ and then merge. Which means we will have to add that within each PQ pipeline. I will let you know if we find another solution.
Joke Laukens 29 on at

Like (0)

Report

Best practice in MSM: Usage of mapping tables

We used the same approach.

Connect to table with mapping during Power Query pipeline and retrieve correct value via merge statement.

Curious for other approaches.
Verified answer

Ivan (Vanya) Kashperuk on at

Like (0)

Report

Best practice in MSM: Usage of mapping tables

Generally speaking, yes, this seems like a viable approach.

I'd be curious to read, what the ideal solution/behavior would be for you here, that would not require modifying every PQ connection you have?

Thanks