Best practice in MSM: Usage of mapping tables

(4) Share

Report

Posted on by Lea Kraus

165

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

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Ivan (Vanya) Kashperuk on at

Like (0)

Report

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

Was this reply helpful? Yes No
Joke Laukens 142 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Lea Kraus 165 on at

Like (1)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Best practice in MSM: Usage of mapping tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Microsoft Cloud for Sustainability | MSM, SDSF, EID

Featured topics

Product updates

Community site session details

Best practice in MSM: Usage of mapping tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Microsoft Cloud for Sustainability | MSM, SDSF, EID

Featured topics

Product updates