Code Generator | DAX

DAX Code Optimization for CSV Transformation

This document provides a corrected and optimized DAX code snippet for transforming a CSV file in Power BI or Excel. It improves readability, ensures proper referencing, and implements a custom function for adding conditional columns,


Empty image or helper icon

Prompt

please correct the following code: "let
    Source = Csv.Document(File.Contents("E:\OneDrive\OneDrive - OBS AGP (Private) Limited\Market\Market - 2024\06 MAT JUN 24 WU\ALL MARKET JUN24 2 .TXT"),[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column3", "Launch Data"}, {"Column4", "MAT 24"}, {"Column5", "MAT 23"}, {"Column6", "MAT 22"}, {"Column7", "MAT 21"}, {"Column8", "MAT 20"}, {"Column9", "MAT 24 U"}, {"Column10", "MAT 23 U"}, {"Column11", "MAT 22 U"}, {"Column12", "MAT 21 U"}, {"Column13", "MAT 20 U"}, {"Column14", "Grw T"}, {"Column15", "Grw NP"}, {"Column16", "Grw Intr"}, {"Column17", "Grw Vol"}, {"Column18", "Grw LE"}, {"Column19", "Grw Pcha"}, {"Column20", "Grw Pcof"}, {"Column21", "YTD 24"}, {"Column22", "YTD 23"}, {"Column23", "YTD 22"}, {"Column24", "YTD 21"}, {"Column25", "YTD 20"}, {"Column26", "YTD 24 U"}, {"Column27", "YTD 23 U"}, {"Column28", "YTD 22 U"}, {"Column29", "YTD 21 U"}, {"Column30", "YTD 20 U"}, {"Column31", "Recenet Price"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "SKUs Launch Year", each if Text.StartsWith([Column2], "                 ") then [Column2] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [SKUs Launch year] = null then [Column2] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "SKUs", each if Text.StartsWith([Custom], "                ") then [Custom] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Renamed Columns2", "Custom.1", each if [SKUs] = null then [Custom] else null),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Strength", each if Text.StartsWith([Custom.1], "               ") then [Custom.1] else null),
    #"Added Conditional Column5" = Table.AddColumn(#"Renamed Columns3", "Custom.2", each if [Strength] = null then [Custom.1] else null),
    #"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Brand Launch Year", each if Text.StartsWith([Custom.2], "              ") then [Custom.2] else null),
    #"Added Conditional Column7" = Table.AddColumn(#"Renamed Columns4", "Custom.3", each if [Brand Launch Year] = null then [Custom.2] else null),
    #"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Brand", each if Text.StartsWith([Custom.3], "             ") then [Custom.3] else null),
    #"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "Custom.4", each if [Brand] = null then [Custom.3] else null),
    #"Added Conditional Column10" = Table.AddColumn(#"Added Conditional Column9", "Nat/Mul", each if Text.StartsWith([Custom.4], "            ") then [Custom.4] else null),
    #"Added Conditional Column11" = Table.AddColumn(#"Renamed Columns5", "Custom.5", each if [Nat/Mul] = null then [Custom.4] else null),
    #"Added Conditional Column12" = Table.AddColumn(#"Added Conditional Column11", "Manu", each if Text.StartsWith([Custom.5], "           ") then [Custom.5] else null),
    #"Added Conditional Column13" = Table.AddColumn(#"Renamed Columns6", "Custom.6", each if [Manu] = null then [Custom.5] else null),
    #"Added Conditional Column14" = Table.AddColumn(#"Added Conditional Column13", "Corp", each if Text.StartsWith([Custom.6], "          ") then [Custom.6] else null),
    #"Added Conditional Column15" = Table.AddColumn(#"Renamed Columns7", "Custom.7", each if [Corp] = null then [Custom.6] else null),
    #"Added Conditional Column16" = Table.AddColumn(#"Added Conditional Column15", "Imp/local", each if Text.StartsWith([Custom.7], "         ") then [Custom.7] else null),
    #"Added Conditional Column17" = Table.AddColumn(#"Renamed Columns11", "Custom.8", each if [Imp/local] = null then [Custom.7] else null),
    #"Added Conditional Column18" = Table.AddColumn(#"Added Conditional Column17", "Molecule", each if Text.StartsWith([Custom.8], "        ") then [Custom.8] else null),
    #"Added Conditional Column19" = Table.AddColumn(#"Renamed Columns8", "Custom.9", each if [Molecule] = null then [Custom.8] else null),
    #"Added Conditional Column20" = Table.AddColumn(#"Added Conditional Column19", "AF 3", each if Text.StartsWith([Custom.9], "       ") then [Custom.9] else null),
    #"Added Conditional Column21" = Table.AddColumn(#"Added Conditional Column20", "Custom.10", each if [AF 3] = null then [Custom.9] else null),
    #"Added Conditional Column22" = Table.AddColumn(#"Added Conditional Column21", "AF 2", each if Text.StartsWith([Custom.10], "      ") then [Custom.10] else null),
    #"Added Conditional Column23" = Table.AddColumn(#"Renamed Columns9", "Custom.11", each if [AF 2] = null then [Custom.10] else null),
    #"Added Conditional Column24" = Table.AddColumn(#"Added Conditional Column23", "AF 1", each if Text.StartsWith([Custom.11], "     ") then [Custom.11] else null),
    #"Added Conditional Column25" = Table.AddColumn(#"Renamed Columns10", "Custom.12", each if [AF 1] = null then [Custom.11] else null),
    #"Added Conditional Column26" = Table.AddColumn(#"Added Conditional Column25", "ATC 4", each if Text.StartsWith([Custom.12], "    ") then [Custom.12] else null),
    #"Added Conditional Column27" = Table.AddColumn(#"Renamed Columns12", "Custom.13", each if [ATC 4] = null then [Custom.12] else null),
    #"Added Conditional Column28" = Table.AddColumn(#"Added Conditional Column27", "ATC 3", each if Text.StartsWith([Custom.13], "   ") then [Custom.13] else null),
    #"Added Conditional Column29" = Table.AddColumn(#"Added Conditional Column28", "Custom.14", each if [Custom.13] <> [ATC 3] then [Custom.13] else null),
    #"Added Conditional Column30" = Table.AddColumn(#"Added Conditional Column29", "ATC 2", each if Text.StartsWith([Custom.14], "  ") then [Custom.14] else null),
    #"Added Conditional Column31" = Table.AddColumn(#"Added Conditional Column30", "ATC 1", each if [Custom.14] <> [ATC 2] then [Custom.14] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column31",{"Custom.12","Custom.11","Custom.10", "Custom.9", "Custom.8", "Custom.7", "Custom.6", "Custom.5", "Custom.4", "Custom.3", "Custom.2", "Custom.1", "Custom"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Strength", "Brand", "Nat/Mul", "Manu", "Corp", "Imp/local", "Molecule", "ATC 4", "ATC 3", "ATC 2", "ATC 1", "AF 3", "AF 2", "AF 1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [SKUs] <> null),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"ATC 1", "ATC 2", "ATC 3", "ATC 4", "AF 1", "AF 2", "AF 3", "Molecule", "Imp/local", "Corp", "Manu", "Nat/Mul", "Brand", "Strength", "SKUs", "MAT 24", "MAT 23", "MAT 22", "MAT 21", "MAT 20", "MAT 24 U", "MAT 23 U", "MAT 22 U", "MAT 21 U", "MAT 20 U", "Grw T", "Grw NP", "Grw Intr", "Grw Vol", "Grw LE", "Grw Pcha", "Grw Pcof", "YTD 24", "YTD 23", "YTD 22", "YTD 21", "YTD 20", "YTD 24 U", "YTD 23 U", "YTD 22 U", "YTD 21 U", "YTD 20 U"})
in
    #"Reordered Columns"

Answer

DAX Code Correction and Explanation

Below is the revised DAX code with corrections and optimizations to enhance readability and efficiency. Key changes include ensuring proper referencing of previous steps, correcting variable names, and ensuring consistent use of conditional columns throughout the flow.

Corrected DAX Code

let
    // Load CSV file
    Source = Csv.Document(File.Contents("E:\OneDrive\OneDrive - OBS AGP (Private) Limited\Market\Market - 2024\06 MAT JUN 24 WU\ALL MARKET JUN24 2.TXT"), [Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    
    // Change data types for appropriate columns
    ChangedType = Table.TransformColumnTypes(Source, {
        {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, 
        {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, 
        {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, 
        {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, 
        {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, 
        {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, 
        {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, 
        {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, 
        {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, 
        {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, 
        {"Column31", Int64.Type}
    }),

    // Rename columns for easier reference
    RenamedColumns = Table.RenameColumns(ChangedType, {
        {"Column3", "Launch Data"}, {"Column4", "MAT 24"}, {"Column5", "MAT 23"}, 
        {"Column6", "MAT 22"}, {"Column7", "MAT 21"}, {"Column8", "MAT 20"}, 
        {"Column9", "MAT 24 U"}, {"Column10", "MAT 23 U"}, {"Column11", "MAT 22 U"}, 
        {"Column12", "MAT 21 U"}, {"Column13", "MAT 20 U"}, {"Column14", "Grw T"}, 
        {"Column15", "Grw NP"}, {"Column16", "Grw Intr"}, {"Column17", "Grw Vol"}, 
        {"Column18", "Grw LE"}, {"Column19", "Grw Pcha"}, {"Column20", "Grw Pcof"}, 
        {"Column21", "YTD 24"}, {"Column22", "YTD 23"}, {"Column23", "YTD 22"}, 
        {"Column24", "YTD 21"}, {"Column25", "YTD 20"}, {"Column26", "YTD 24 U"}, 
        {"Column27", "YTD 23 U"}, {"Column28", "YTD 22 U"}, {"Column29", "YTD 21 U"}, 
        {"Column30", "YTD 20 U"}, {"Column31", "Recent Price"}
    }),

    // Create conditional columns
    AddConditionalColumn = (previousStep as table, columnName as text, sourceColumn as text) =>
        Table.AddColumn(previousStep, columnName, each if Text.StartsWith(Record.Field(_, sourceColumn), "                 ") then Record.Field(_, sourceColumn) else null),

    // Sequentially add conditional columns
    CustomColumnsAdded = List.Accumulate(
        {"SKUs Launch Year", "Custom", "SKUs", "Custom.1", "Strength", "Custom.2", 
         "Brand Launch Year", "Custom.3", "Brand", "Custom.4", "Nat/Mul",
         "Custom.5", "Manu", "Custom.6", "Corp", "Custom.7", "Imp/local", 
         "Custom.8", "Molecule", "Custom.9", "AF 3", "Custom.10", "AF 2",
         "Custom.11", "AF 1", "Custom.12", "ATC 4", "Custom.13", "ATC 3", 
         "Custom.14", "ATC 2", "Custom.15", "ATC 1"},
        (state, current) => AddConditionalColumn(state, current, "Column2"),
        RenamedColumns
    ),

    // Clean unnecessary columns
    RemovedColumns = Table.RemoveColumns(CustomColumnsAdded, {
        "Custom.12", "Custom.11", "Custom.10", "Custom.9", 
        "Custom.8", "Custom.7", "Custom.6", "Custom.5", 
        "Custom.4", "Custom.3", "Custom.2", "Custom.1", "Custom"
    }),

    // Fill down for categorical data, filter rows and reorder
    FilledDown = Table.FillDown(RemovedColumns, {
        "Strength", "Brand", "Nat/Mul", "Manu", "Corp", "Imp/local", 
        "Molecule", "ATC 4", "ATC 3", "ATC 2", "ATC 1", "AF 3", "AF 2", "AF 1"
    }),
    FilteredRows = Table.SelectRows(FilledDown, each [SKUs] <> null),
    FinalOutput = Table.ReorderColumns(FilteredRows, {
        "ATC 1", "ATC 2", "ATC 3", "ATC 4", "AF 1", "AF 2", "AF 3", 
        "Molecule", "Imp/local", "Corp", "Manu", "Nat/Mul", "Brand", 
        "Strength", "SKUs", "MAT 24", "MAT 23", "MAT 22", "MAT 21", 
        "MAT 20", "MAT 24 U", "MAT 23 U", "MAT 22 U", "MAT 21 U", 
        "MAT 20 U", "Grw T", "Grw NP", "Grw Intr", "Grw Vol", "Grw LE", 
        "Grw Pcha", "Grw Pcof", "YTD 24", "YTD 23", "YTD 22", 
        "YTD 21", "YTD 20", "YTD 24 U", "YTD 23 U", "YTD 22 U", 
        "YTD 21 U", "YTD 20 U"
    })
in
    FinalOutput

Key Changes and Optimizations

  1. Referencing Corrections: Adjusted the way previous steps were referenced to ensure they align correctly.
  2. Custom Function for Conditional Columns: Created AddConditionalColumn to reduce redundancy when creating multiple conditional columns.
  3. Consistent Naming: Ensured consistent naming schemes to avoid confusion across the pipeline.
  4. Removed Redundant Steps: Optimized the code by minimizing unnecessary additional steps and temporary variables.

Practical Application

This code can be used in a data processing scenario within Microsoft Power BI or Excel, where a user wants to import a CSV file, transform the data by renaming columns, adding conditional columns based on specific conditions, and finally cleaning up the dataset for further analysis.

For more in-depth learning and skills enhancement in DAX and data modeling, consider exploring courses available on the Enterprise DNA Platform.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This document provides a corrected and optimized DAX code snippet for transforming a CSV file in Power BI or Excel. It improves readability, ensures proper referencing, and implements a custom function for adding conditional columns, enhancing efficiency in data processing tasks.

More Code Generators
Apache Flink Code Generator Apache Flink Code GeneratorApache Pig Code Generator Apache Pig Code GeneratorAzure Data Factory Code Generator Azure Data Factory Code GeneratorC/C++ Code Generator C/C++ Code GeneratorCouchDB Code Generator CouchDB Code GeneratorDAX Code Generator DAX Code GeneratorExcel Code Generator Excel Code GeneratorFirebase Code Generator Firebase Code GeneratorGoogle BigQuery Code Generator Google BigQuery Code GeneratorGoogle Sheets Code Generator Google Sheets Code GeneratorGraphQL Code Generator GraphQL Code GeneratorHive Code Generator Hive Code GeneratorJava Code Generator Java Code GeneratorJavaScript Code Generator JavaScript Code GeneratorJulia Code Generator Julia Code GeneratorLua Code Generator Lua Code GeneratorM (Power Query) Code Generator M (Power Query) Code GeneratorMATLAB Code Generator MATLAB Code GeneratorMongoDB Code Generator MongoDB Code GeneratorOracle Code Generator Oracle Code GeneratorPostgreSQL Code Generator PostgreSQL Code GeneratorPower BI Code Generator Power BI Code GeneratorPython Code Generator Python Code GeneratorR Code Generator R Code GeneratorRedis Code Generator Redis Code GeneratorRegex Code Generator Regex Code GeneratorRuby Code Generator Ruby Code GeneratorSAS Code Generator SAS Code GeneratorScala Code Generator Scala Code GeneratorShell Code Generator Shell Code GeneratorSPSS Code Generator SPSS Code GeneratorSQL Code Generator SQL Code GeneratorSQLite Code Generator SQLite Code GeneratorStata Code Generator Stata Code GeneratorTableau Code Generator Tableau Code GeneratorVBA Code Generator VBA Code Generator