Tools Advisor | Power BI

Enhancing Power BI M Code Efficiency

This guide presents an optimized version of Power BI M code, focusing on improving readability and performance by reducing redundancy, simplifying column selections, and consolidating transformations and filtering operations.

Prompt

let
    Source = Sql.Database("USNPDEDWDBP01", "P2M"),
    dbo_lp_Fcst_ParentLevel = Source{[Schema="dbo",Item="lp_Fcst_ParentLevel"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(#"dbo_lp_Fcst_ParentLevel", "PlaceholderNumber", "PlaceholderNumber - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "PlaceholderNumber - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"PlaceholderNumber - Copy.1", "PlaceholderNumber - Copy.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Character Transition",{"PlaceholderNumber - Copy.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"PlaceholderNumber - Copy.2", "PLCNumber"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"ExtractID", "BlindBagCharacterCountTotal", "BlindBagFall", "BlindBagSpring", "CfFall", "CfSpring", "Description", "DigitalProduct", "MarketingChannel", "Domestic", "DomPrice", "eCommAstSolid", "eCommFall", "eCommSpring", "eCommTotal", "FamilyBrand", "Gender", "GlobalAsiaForecastSalesGross", "GlobalAsiaForecastSalesNet", "GlobalAsiaForecastUnits", "GlobalEUForecastSalesGross", "GlobalEUForecastSalesNet", "GlobalEUForecastUnits", "GlobalForecastSalesGross", "GlobalForecastSalesNet", "GlobalLAMForecastSalesGross", "GlobalLAMForecastSalesNet", "GlobalLAMForecastUnits", "GlobalNAForecastSalesGross", "GlobalNAForecastSalesNet", "GlobalNAForecastUnits", "GlobalPacificForecastSalesGross", "GlobalPacificForecastSalesNet", "GlobalPacificForecastUnits", "GSPlush", "IndustryShortDescription", "IPSensitivity", "SoftProjectile", "ItemCountTotalFall", "ItemCountTotalSpring", "LcPrice", "LowerAge", "Movie", "NewFall", "NewSpring", "PkgRefFall", "PkgRefSpring", "PrimeTimeOrMustHave", "ProductRefFall", "ProductRefSpring", "Property", "RegionalAsiaPriority", "RegionalAsiaRetailerExclusive", "RegionalEUPriority", "RegionalEURetailerExclusive", "RegionalFcstAsiaGrossSales", "RegionalFcstAsiaNetSales", "RegionalFcstAsiaUnit", "RegionalFcstEUGrossSales", "RegionalFcstEUNetSales", "RegionalFcstEUUnit", "RegionalFcstLAMGrossSales", "RegionalFcstLAMNetSales", "RegionalFcstLAMUnit", "RegionalFcstNAGrossSales", "RegionalFcstNANetSales", "RegionalFcstNAUnit", "RegionalFcstPacificGrossSales", "RegionalFcstPacificNetSales", "RegionalFcstPacificUnit", "RegionalLAMPriority", "RegionalLAMRetailerExclusive", "RegionalNAPriority", "RegionalNARetailerExclusive", "RegionalPacificPriority", "RegionalPacificRetailerExclusive", "RetailerDistribution", "RetailItemCountTotal", "SoldasSolidFall", "SoldasSolidSpring", "SoldasSolidTotal", "SuperCategory", "ThirdPartyIndicator", "TradeMarketingPallet", "TvAd", "UpdateProductnametoPlan", "UpperAge", "FlexPlanID", "PSLinkID", "ProdSeaRemoved", "RegionalAsiaDistributionChannel", "RegionalAsiaExclusivity", "RegionalAsiaRetailChannel", "RegionalPacificDistributionChannel", "RegionalPacificExclusivity", "RegionalPacificRetailChannel", "RegionalEUDistributionChannel", "RegionalEUExclusivity", "RegionalEURetailChannel", "RegionalLAMDistributionChannel", "RegionalLAMExclusivity", "RegionalLAMRetailChannel", "RegionalNADistributionChannel", "RegionalNAExclusivity", "RegionalNARetailChannel", "RegionalAsiaSeasonality", "RegionalPacificSeasonality", "RegionalEUSeasonality", "RegionalLAMSeasonality", "RegionalNASeasonality", "RegionalAsiaPriorityDirection", "RegionalPacificPriorityDirection", "RegionalEUPriorityDirection", "RegionalLAMPriorityDirection", "RegionalNAPriorityDirection", "Comments", "AsiaBUFcstUnit", "EUBUFcstUnit", "LAMBUFcstUnit", "NABUFcstUnit", "PacificBUFcstUnit", "AsiaEstBUGrossSales", "EUEstBUGrossSales", "LAMEstBUGrossSales", "NAEstBUGrossSales", "PacificEstBUGrossSales", "AsiaEstBUNetSales", "EUEstBUNetSales", "LAMEstBUNetSales", "NAEstBUNetSales", "PacificEstBUNetSales", "PulseAsiaBUFcstUnit", "PulseEUBUFcstUnit", "PulseLAMBUFcstUnit", "PulseNABUFcstUnit", "PulsePacificBUFcstUnit", "PulseAsiaEstBUGrossSales", "PulseEUEstBUGrossSales", "PulseLAMEstBUGrossSales", "PulseNAEstBUGrossSales", "PulsePacificEstBUGrossSales", "PulseAsiaEstBUNetSales", "PulseEUEstBUNetSales", "PulseLAMEstBUNetSales", "PulseNAEstBUNetSales", "PulsePacificEstBUNetSales", "AsiaGrandTotalBUFcstUnits", "EUGrandTotalBUFcstUnits", "LAMGrandTotalBUFcstUnits", "NAGrandTotalBUFcstUnits", "PacificGrandTotalBUFcstUnits", "AsiaGrandTotalEstBUGrossSales", "EUGrandTotalEstBUGrossSales", "LAMGrandTotalEstBUGrossSales", "NAGrandTotalEstBUGrossSales", "PacificGrandTotalEstBUGrossSales", "AsiaGrandTotalEstBUNetSales", "EUGrandTotalEstBUNetSales", "LAMGrandTotalEstBUNetSales", "NAGrandTotalEstBUNetSales", "PacificGrandTotalEstBUNetSales", "RegionalPriority", "GlobalMediaDriver", "FanConsumerTarget", "GreatGift", "CriticalCore"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"CfTotal", type text}, {"NewTotal", type text}, {"ProductRefTotal", type text}, {"PkgRefTotal", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"CfTotal", "CfTotal-DTYPETEXT"}, {"NewTotal", "NewTotal-DTYPETEXT"}, {"PkgRefTotal", "PkgRefTotal-DTYPETEXT"}, {"ProductRefTotal", "ProductRefTotal--DTYPETEXT"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns2", "CfTotal-DTYPETEXT", "CfTotal-DTYPETEXT - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"CfTotal-DTYPETEXT - Copy", type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"CfTotal-DTYPETEXT - Copy", "CfTotal"}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns3", "NewTotal-DTYPETEXT", "NewTotal-DTYPETEXT - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column2",{{"NewTotal-DTYPETEXT - Copy", type number}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Changed Type2",{{"NewTotal-DTYPETEXT - Copy", "NewTotal"}}),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Renamed Columns4", "ProductRefTotal--DTYPETEXT", "ProductRefTotal--DTYPETEXT - Copy"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Duplicated Column3",{{"ProductRefTotal--DTYPETEXT - Copy", type number}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Changed Type3",{{"ProductRefTotal--DTYPETEXT - Copy", "ProductRefTotal"}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns5", "PkgRefTotal-DTYPETEXT", "PkgRefTotal-DTYPETEXT - Copy"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column4",{{"PkgRefTotal-DTYPETEXT - Copy", type number}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Changed Type4",{{"PkgRefTotal-DTYPETEXT - Copy", "PkgRefTotal"}}),
    #"Duplicated Column5" = Table.DuplicateColumn(#"Renamed Columns6", "Srp", "Srp - Copy"),
    #"Changed Type5" = Table.TransformColumnTypes(#"Duplicated Column5",{{"Srp - Copy", type text}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Changed Type5",{{"Srp - Copy", "Srp - DTYPETEXT"}}),
    #"Duplicated Column6" = Table.DuplicateColumn(#"Renamed Columns7", "TotalGlobalForecastUnits", "TotalGlobalForecastUnits - Copy"),
    #"Changed Type6" = Table.TransformColumnTypes(#"Duplicated Column6",{{"TotalGlobalForecastUnits - Copy", type text}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Changed Type6",{{"TotalGlobalForecastUnits - Copy", "TotalGlobalForecastUnits -DTYPETEXT"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns8",{"ID", "AssortmentReference", "AstOrSolid", "Class", "CoBrand", "GlobalSKU", "InnovationType", "Onshelfdate", "PlanStatus", "ProductType", "ProjectType", "Segment", "SolidReference", "TargetCost", "ProductID", "PlaceholderID", "eCommPDPPriority"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns3", each ([Season] = "2025" or [Season] = "2026")),
    #"Changed Type7" = Table.TransformColumnTypes(#"Filtered Rows",{{"Srp - DTYPETEXT", type number}, {"TotalGlobalForecastUnits -DTYPETEXT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type7",{{"Srp", "SRP-DTYPETEXT"}, {"TotalGlobalForecastUnits", "TotalGlobalForecastUnits-DTYPETEXT"}, {"Srp - DTYPETEXT", "SRP"}, {"TotalGlobalForecastUnits -DTYPETEXT", "TotalGlobalForecastUnits"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"AddedtoSeasonOn", "CreateDate", "Creator", "ModifiedDate", "Modifier", "createDateTime", "lastUpdateDateTime", "PlaceholderStatus"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Season] = "2025"))
in
    #"Filtered Rows1" //OPTIMIZE THE CODE

Answer

Optimizing Power BI M Code

The provided M code in Power Query for Power BI is quite extensive and includes several steps that can be optimized to enhance its readability and performance. Below is a structured approach to optimizing the script with explanations for each step.

Key Areas for Optimization

Reduce Redundant Steps: Many duplications and unnecessary renaming of columns can be streamlined.
Minimize Column Duplication: Instead of creating copies for transformations, directly change column types if applicable.
Combine Filter and Removal Steps: Merging similar data trimming and filtering operations into single steps will enhance performance.

Optimized M Code

Below is the optimized version of the provided M code.

let
    // Load data from SQL
    Source = Sql.Database("USNPDEDWDBP01", "P2M"),
    dbo_lp_Fcst_ParentLevel = Source{[Schema="dbo", Item="lp_Fcst_ParentLevel"]}[Data],

    // Duplicate and split the PlaceholderNumber column
    PlaceholderSplit = Table.SplitColumn(
        dbo_lp_Fcst_ParentLevel, 
        "PlaceholderNumber", 
        Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), 
        {"PLCNumberPrefix", "PLCNumber"}
    ),

    // Select and rename columns while removing unnecessary ones
    DesiredColumns = Table.SelectColumns(PlaceholderSplit, {
        "PLCNumber", "CfTotal", "NewTotal", "ProductRefTotal", "PkgRefTotal", 
        "Srp", "TotalGlobalForecastUnits", "Season"
    }),

    // Change data types in a single step
    ChangedTypes = Table.TransformColumnTypes(DesiredColumns, {
        {"CfTotal", type number}, 
        {"NewTotal", type number}, 
        {"ProductRefTotal", type number}, 
        {"PkgRefTotal", type number}, 
        {"Srp", type number}, 
        {"TotalGlobalForecastUnits", type number}
    }),

    // Filter rows for specific seasons in one step
    FilteredRows = Table.SelectRows(ChangedTypes, each [Season] = "2025" or [Season] = "2026")
    
in
    FilteredRows

Explanation of Changes

Removed Redundant Duplicates:
- The original code had excessive duplication of columns for type conversion. In the optimized code, the key columns were transformed directly without intermediate duplicates.
Simplified Column Selection:
- The code now uses Table.SelectColumns to retain only the necessary columns in one step rather than removing them one by one.
Unified Type Changes:
- A single Table.TransformColumnTypes statement was used to change the data types for all columns needing conversion. This reduces the number of steps and consolidates type transformations.
Consolidated Filtering:
- The season filtering was conducted with a single step rather than creating multiple filtered datasets leading to unwarranted complexity.

Conclusion

This optimized version of your Power BI M script is more efficient and easier to understand. By reducing redundancy and combining operations, the overall performance and maintainability of your Power BI data model are improved.

If you need further enhancements or more specific transformations, consider courses available at Enterprise DNA to deepen your understanding of Power Query efficiency.