Get transformation rules from a Mapping table

Power Query
1

Hi All,
One of my queries uses a function that consumes values provided to it by a variable named transformationRules that has a structure as follows:

transformationRules =
[
X1 = [ Y1 = “Z”],
X2 = [Y2 = “Z”],
X3 = [Y3 = “Z”]
]

I’ve been hard coding the values of X and Y, but I’d rather use a mapping table that maps X to Y (since there are many rules)

Could someone help me figure out how to loop through each row of the mapping table, inserting X and Y values into the record above?

I’m getting close to an elegant solution to what has been a challenging data structure reshaping project, and this would help get a step closer.

(I know AI could easily help with this, but my company’s policy prohibits its use).

Julie

2

Hi @JulieCarignan,

To address the request regarding the transformation rules structure, the goal is to automate the creation of a mapping from a predefined table instead of hard-coding values. Here’s a simple explanation on how to accomplish that task using a loop.

Structure of the Transformation Rules

The transformation rules provided have a structure where each X corresponds to a list containing a single mapping of Y and its value:

transformationRules =
[
    X1 = [ Y1 = “Z”],
    X2 = [ Y2 = “Z”],
    X3 = [ Y3 = “Z”]
]

Desired Outcome

The aim is to replace hard-coded values of X and Y with values from a mapping table. This table likely consists of pairs of X and Y that need to be iterated over to dynamically generate the transformation rules.

Mapping Table

Assuming your mapping table looks something like this in code:

mappingTable = [
    { "X": "X1", "Y": "Y1", "Value": "Z" },
    { "X": "X2", "Y": "Y2", "Value": "Z" },
    { "X": "X3", "Y": "Y3", "Value": "Z" }
]

Loop Implementation

Below, I describe how to loop through this mapping table and construct your transformation rules.

Example Code in Python

# Initialize an empty dictionary to hold your transformation rules
transformationRules = {}

# Example mapping table
mappingTable = [
    {"X": "X1", "Y": "Y1", "Value": "Z"},
    {"X": "X2", "Y": "Y2", "Value": "Z"},
    {"X": "X3", "Y": "Y3", "Value": "Z"}
]

# Iterate through each entry in the mapping table
for entry in mappingTable:
    x_value = entry["X"]
    y_value = entry["Y"]
    value = entry["Value"]
    
    # Construct the transformation rule
    transformationRules[x_value] = [{y_value: value}]

# transformationRules now contains the desired mapping structure
print(transformationRules)

Explanation of the Code

  • Initialization: The transformationRules variable is created as an empty dictionary to store the mappings.
  • Mapping Table: This example uses a list of dictionaries where each dictionary holds an X, Y, and its corresponding value.
  • Loop: The for loop goes through each dictionary in mappingTable. For each entry:
    • The values of X, Y, and the associated value are extracted.
    • A new key-value pair is created in transformationRules where X is the key, and the value is a list containing a dictionary that maps Y to its associated value.
  • Output: Finally, the transformationRules will reflect the dynamically created mappings based on the mapping table.

Conclusion

This approach allows for more flexibility in managing the transformation rules. You can easily update or add new mappings in the mappingTable without modifying the core logic of your transformation rule creation. By iterating through each item, you elegantly populate the desired structure based on external data, making your code cleaner and more maintainable.

Source:https://mentor.enterprisedna.co/explain-simply

Cheers,

Enterprise DNA Support Team

3

Thank you.

Is there any chance you could provide the example code in M (Power Query) rather than Python?

Julie

4

I believe you can you use Mentor (built within the EDNA platform) that can switch the coding into M(powery Query)

Please give Mentor a try to help you.

thanks
Keith

5

Hi Keith,
I don’t have a mentor subscription (I have only a Learn subscription).

Julie

6

i believe learning subscription had some mentor involved

7

Hi @JulieCarignan,

You have 15 free Data Mentor queries per month as part of your allocation.

image
image721×111 5.5 KB

Regarding your question, we tested the Data Mentor “Code Explainer” to convert Python code to M Code—you might find it useful to try it yourself! You can also explore more of Data Mentor’s features for a broader experience.

image
image1433×732 114 KB

image
image1557×956 119 KB

let
// Initialize the mapping table
mappingTable = {
[X=“X1”, Y=“Y1”, Value=“Z”],
[X=“X2”, Y=“Y2”, Value=“Z”],
[X=“X3”, Y=“Y3”, Value=“Z”]
},

// Generate transformation rules
transformationRules = List.Accumulate(
    mappingTable,
    [],
    (state, entry) => 
        let
            x_value = Record.Field(entry, "X"),
            y_value = Record.Field(entry, "Y"),
            value = Record.Field(entry, "Value"),
            rule = [y_value = value]
        in
            if List.AnyTrue(List.Transform(state, each _[x_value] <> null)) then
                state // If x_value already exists, do not add a new entry
            else
                List.Combine({state, {{x_value, {rule}}}}) // Add new x_value with the corresponding rule
),

// Convert transformation rules to a record format if desired for easier access
transformationOutput = Record.FromList(
    List.Transform(transformationRules, each Record.ToList(_)),
    List.Transform(transformationRules, each Record.Field(_, 0))
)

in
transformationOutput

For full access, you may want to check out our Data Mentor à la carte offers on the pricing page:
:link: Data Mentor Pricing

Cheers,

Enterprise DNA Support Team