Data Transformation Function in M (Power Query)

This guide details the implementation of a data transformation function in M (Power Query) that performs standardized transformations across multiple tables. By encapsulating the transformation logic in a function, we ensure code reusability and scalability.

Function Overview

The function will process tables by:

1. Expanding specific columns.
2. Removing irrelevant columns.
3. Changing data types.
4. Replacing specific values.
5. Adding calculated columns.
6. Reordering, removing, and pivoting columns as per specified logic.

Code Implementation

let
    // Main function to perform transformations on input table
    TransformTable = (InputTable as table) as table =>
    let
        // Step 1: Expand the needed columns
        ExpandedTable = Table.ExpandTableColumn(InputTable, "Benutzerdefiniert", 
            {"Auftragsnummer", "Status Beschreibung", "Auftragsdatum", "Klassifizierung Beschreibung", 
             "Typ Beschreibung", "Kfz-Kennzeichen", "Kilometerstand Auftrag", "Laufleistung Gesamt", 
             "Einsteuerungsdatum", "Geplantes Aussteuerungsdatum", "Aussteuerungsdatum", 
             "Kraftstoff Quelle", "Fahrzeug Materialnummer", "Marke Beschreibung", "Model Beschreibung", 
             "Variante Beschreibung", "Rechnungssteller", "Name Rechnungssteller", "Postleitzahl", 
             "Servicelieferant", "Name Service Lieferant", "Rahmenvertrag", "Bezeichnung Firma", 
             "Bezeichnung Niederlassung", "Technischer Platz", "MM Bestellung", "Reifen Anlage", 
             "GW Anlage", "Wartung", "Reparaturen", "HU/AU", "Reifen/Räder", "Reifenservice", 
             "Abschleppservice", "Ersatzmobilität", "Hol-und Bringservice", "Reinigung/Wäsche", 
             "Gutachten", "Standgebühren", "Sondergenehmigung", "Nachrüstung", "An/Abmeldung", 
             "Transport", "Beklebung", "Sonstige Konto", "Genehmigter Betrag", "Fleet Zul.", 
             "Erstzulassungsdatum", "Produktgruppe", "Eingest. als Produkt", "Eingest. als Kategorie", 
             "Fahrzeugunterklasse"}, 
            {"Auftragsnummer", "Status Beschreibung", "Auftragsdatum", "Klassifizierung Beschreibung", 
             "Typ Beschreibung", "Kfz-Kennzeichen", "Kilometerstand Auftrag", "Laufleistung Gesamt", 
             "Einsteuerungsdatum", "Geplantes Aussteuerungsdatum", "Aussteuerungsdatum", 
             "Kraftstoff Quelle", "Fahrzeug Materialnummer", "Marke Beschreibung", "Model Beschreibung", 
             "Variante Beschreibung", "Rechnungssteller", "Name Rechnungssteller", "Postleitzahl", 
             "Servicelieferant", "Name Service Lieferant", "Rahmenvertrag", "Bezeichnung Firma", 
             "Bezeichnung Niederlassung", "Technischer Platz", "MM Bestellung", "Reifen Anlage", 
             "GW Anlage", "Wartung", "Reparaturen", "HU/AU", "Reifen/Räder", "Reifenservice", 
             "Abschleppservice", "Ersatzmobilität", "Hol-und Bringservice", "Reinigung/Wäsche", 
             "Gutachten", "Standgebühren", "Sondergenehmigung", "Nachrüstung", "An/Abmeldung", 
             "Transport", "Beklebung", "Sonstige Konto", "Genehmigter Betrag", "Fleet Zul.", 
             "Erstzulassungsdatum", "Produktgruppe", "Eingest. als Produkt", "Eingest. als Kategorie", 
             "Fahrzeugunterklasse"}),

        // Step 2: Remove unnecessary columns
        RemovedColumns = Table.RemoveColumns(ExpandedTable, {"Kilometerstand Auftrag", "Laufleistung Gesamt", 
            "Einsteuerungsdatum", "Geplantes Aussteuerungsdatum", "Aussteuerungsdatum", "Kraftstoff Quelle", 
            "Rechnungssteller", "Name Rechnungssteller", "Postleitzahl"}),

        // Step 3: Change data types
        ChangedTypes = Table.TransformColumnTypes(RemovedColumns, 
            {{"Auftragsdatum", type date}, {"Geschäftsjahr", type text}, {"Auftragsnummer", type text}, 
             {"Status Beschreibung", type text}, {"Klassifizierung Beschreibung", type text}, 
             {"Typ Beschreibung", type text}, {"Kfz-Kennzeichen", type text}, {"Fahrzeug Materialnummer", type text}, 
             {"Marke Beschreibung", type text}, {"Model Beschreibung", type text}, 
             {"Variante Beschreibung", type text}, {"Servicelieferant", type text}, 
             {"Name Service Lieferant", type text}, {"Rahmenvertrag", type text}, 
             {"Bezeichnung Firma", type text}, {"Bezeichnung Niederlassung", type text}, 
             {"Technischer Platz", type text}, {"MM Bestellung", type text}, {"Reifen Anlage", type text}, 
             {"GW Anlage", type text}, {"Wartung", Currency.Type}, {"Reparaturen", Currency.Type}, 
             {"HU/AU", Currency.Type}, {"Reifen/Räder", Currency.Type}, {"Reifenservice", Currency.Type}, 
             {"Abschleppservice", Currency.Type}, {"Ersatzmobilität", Currency.Type}, 
             {"Hol-und Bringservice", Currency.Type}, {"Reinigung/Wäsche", Currency.Type}, 
             {"Gutachten", Currency.Type}, {"Standgebühren", Currency.Type}, {"Sondergenehmigung", Currency.Type}, 
             {"Nachrüstung", Currency.Type}, {"An/Abmeldung", Currency.Type}, {"Transport", Currency.Type}, 
             {"Beklebung", Currency.Type}, {"Sonstige Konto", Currency.Type}, {"Genehmigter Betrag", Currency.Type}, 
             {"Fleet Zul.", type date}, {"Erstzulassungsdatum", type date}, 
             {"Produktgruppe", type text}, {"Eingest. als Produkt", type text}, 
             {"Eingest. als Kategorie", type text}, {"Fahrzeugunterklasse", type text}}),

        // Step 4: Replace specific values
        ReplacedValue = Table.ReplaceValue(ChangedTypes, "", "Aufwand", Replacer.ReplaceValue, {"Reifen Anlage"}),
        ReplacedValue1 = Table.ReplaceValue(ReplacedValue, "", "keine GW Anlage", Replacer.ReplaceValue, {"GW Anlage"}),

        // Step 5: Add calculated column
        ConditionalColumn = Table.AddColumn(ReplacedValue1, "Produkt", each if Text.StartsWith([#"Kfz-Kennzeichen"], "BN") then "Gfz" else "Dfz"),
        
        // Change type for the new column
        ChangedType1 = Table.TransformColumnTypes(ConditionalColumn, {{"Produkt", type text}}),

        // Step 6: Reorder columns
        ReorderedColumns = Table.ReorderColumns(ChangedType1, {"Buchungsdatum", "Geschäftsjahr", "Auftragsnummer", 
            "Status Beschreibung", "Auftragsdatum", "Klassifizierung Beschreibung", "Typ Beschreibung", 
            "Kfz-Kennzeichen", "Produkt", "Fahrzeug Materialnummer", "Marke Beschreibung", "Model Beschreibung", 
            "Variante Beschreibung", "Servicelieferant", "Name Service Lieferant", "Rahmenvertrag", 
            "Bezeichnung Firma", "Bezeichnung Niederlassung", "Technischer Platz", "MM Bestellung", 
            "Reifen Anlage", "GW Anlage", "Wartung", "Reparaturen", "HU/AU", "Reifen/Räder", 
            "Reifenservice", "Abschleppservice", "Ersatzmobilität", "Hol-und Bringservice", 
            "Reinigung/Wäsche", "Gutachten", "Standgebühren", "Sondergenehmigung", "Nachrüstung", 
            "An/Abmeldung", "Transport", "Beklebung", "Sonstige Konto", "Genehmigter Betrag", "Fleet Zul.", 
            "Erstzulassungsdatum", "Produktgruppe", "Eingest. als Produkt", "Eingest. als Kategorie", 
            "Fahrzeugunterklasse"}),

        // Step 7: Final adjustments (Remove columns and pivot)
        FinalRemovedColumns = Table.RemoveColumns(ReorderedColumns, {"Genehmigter Betrag"}),
        UnpivotedColumns = Table.UnpivotOtherColumns(FinalRemovedColumns, {"Buchungsdatum", "Geschäftsjahr", 
            "Auftragsnummer", "Status Beschreibung", "Auftragsdatum", "Klassifizierung Beschreibung", 
            "Typ Beschreibung", "Kfz-Kennzeichen", "Produkt", "Fahrzeug Materialnummer", 
            "Marke Beschreibung", "Model Beschreibung", "Variante Beschreibung", "Servicelieferant", 
            "Name Service Lieferant", "Rahmenvertrag", "Bezeichnung Firma", "Bezeichnung Niederlassung", 
            "Technischer Platz", "MM Bestellung", "Reifen Anlage", "GW Anlage", "Fleet Zul.", 
            "Erstzulassungsdatum", "Produktgruppe", "Eingest. als Produkt", 
            "Eingest. als Kategorie", "Fahrzeugunterklasse"}, "Attribut", "Wert"),
        
        RenamedColumns = Table.RenameColumns(UnpivotedColumns, {{"Attribut", "Kostenbereich"}, {"Wert", "Auftragswert"}}),

        // Filtering the resulting rows
        FilteredRows = Table.SelectRows(RenamedColumns, each [Auftragswert] > 0),
        FinalValueReplaced = Table.ReplaceValue(FilteredRows, "X", "Invest", Replacer.ReplaceText, {"Reifen Anlage"})
        
    in
        FinalValueReplaced
in
    TransformTable

Function Parameters

• InputTable: A table to which the transformations will be applied.

Return Type

• Returns a table after performing the defined transformations.

Example Usage

To use the TransformTable function, you can call it with an input table from your data model, like this:

let
    Source = YourDataSourceHere,
    TransformedData = TransformTable(Source)
in
    TransformedData

Conclusion

This function encapsulates a complex data transformation sequence in a reusable way, adhering to best practices in M (Power Query). You can apply it fluidly across multiple input tables to ensure consistency in your data processing tasks. For more comprehensive courses on data transformations, the Enterprise DNA Platform offers a variety of resources to enhance your Power Query skills.