Produces DAX code to debug the variables in a DAX measure.

DAX.VariableDebugger.pq

let func =   
 let
    Source = (columnNames as text, optional topN as number, optional measureCode as text) => 

let 

/* Debug parameters
    measureCode = DAXMeasureCode,
    columnNames = "RankItem,RankMeasure, null, Rank, TopOrOthers, TopN_Others",
    topN = 5,
 End of debug parameters */

     MeasureCode = if measureCode = null then DAXVariableMeasureCode else measureCode,
    ColumnNames = List.Transform(Text.Split(columnNames, ","), (x) => [Cleaned = Text.Trim(Text.Clean(x)), Result = if Cleaned = "null" then null else Cleaned][Result]),
    TopN = if topN = null then 10 else topN,

    fnTextRemoveBetweenDelimiters = 
        (TextToClean as text, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters) =>
            let
                removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else "",
                Source = Text.From(TextToClean),
                FirstSplit = Text.Split(Source, StartDelimiter),
                SecondSplit = List.Combine(List.Transform(FirstSplit, each Text.Split(_, EndDelimiter))),
                ListAlternate = List.Alternate(SecondSplit,1,1,1),
                ListSelect = List.Select(ListAlternate, each _<>""),
                TextCombine = Text.Combine(ListSelect, removeDelimiters)
            in
                TextCombine,

    fnConcatenateTableValues = 
    (filterExpression as text,
    myColumnName as text,
    optional MaxFilters as number,
    optional VarName as text
    ) =>

        let
        /* Debug parameters
            myColumnName = "Datum",
            filterExpression = "DATESYTD(DimDate[Datum])",
            MaxFilters = 10,
        */// End of debug parameters

            maxFilters = Text.From(if MaxFilters = null then 10 else MaxFilters),
            ListOfDelimiters = {">=", "<=", "<>", "=", ">", "<"},
            
            // Splits filterExpression by the first delimiter it finds
            SplittedBoolean = List.Accumulate(  
                                ListOfDelimiters, 
                                {},
                                (state, current) =>  if List.Count(state) = 2 
                                                        then state 
                                                        else Text.Split(filterExpression, current)),
            HasSyntaxSugar = not Text.Contains(
                                    List.First(SplittedBoolean), 
                                    ")"),
            // FullFilterExpression = if HasSyntaxSugar 
            //                        then "Filter(All(" & SplittedBoolean{0} & "), " & filterExpression & ")" 
            //                        else filterExpression,
            FullFilterExpression = filterExpression,
            MeasureString = 
        Text.Replace(VarName, Text.Start(VarName,4), "VAR Debug") & " = 
        VAR FilterTable = " 
            & FullFilterExpression & "
        VAR TableLength = 
            COUNTROWS(FilterTable)
        VAR ColumnMax = 
            MAXX(FilterTable, [" & myColumnName & "])
        VAR ColumnMin = 
            MINX(FilterTable, [" & myColumnName & "])
        VAR TOPX = IF(TableLength < " & maxFilters & ", TableLength, " & maxFilters &")
        VAR ValuesInColumn = 
            CONCATENATEX(TOPN(TOPX, FilterTable), [" & myColumnName & "], "" | "") 
        RETURN
            --""TableLength: "" & TableLength  & "", Min: "" & ColumnMin & "", Max: "" & ColumnMax
            ""TableLength: "" & TableLength  & "", Min: "" & ColumnMin & "", Max: "" & ColumnMax & UNICHAR(13) & UNICHAR(10) & ""Top "" & TOPX & "" Values: "" & ValuesInColumn
                        "
        in
            MeasureString,
    measureCodeWithoutMultilineComments = fnTextRemoveBetweenDelimiters(MeasureCode, "/*", "*/", "Yes"),
    
    
    ConvertMeasureCodeToList = Text.Split(measureCodeWithoutMultilineComments, "#(lf)"),
    ExtractMeasureName = Text.Trim(Text.BeforeDelimiter(ConvertMeasureCodeToList{0}, "=")),
    ConverteMeasureCodeListToTable = Table.FromList(ConvertMeasureCodeToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EliminateComments = Table.AddColumn(ConverteMeasureCodeListToTable, "Custom.1", each Text.BeforeDelimiter([Column1], "//")),
    CleanupExtractedCode = Table.TransformColumns(EliminateComments,{{"Custom.1", Text.Trim, type text}}),
    Cleansup2 = Table.TransformColumns(CleanupExtractedCode,{{"Custom.1", Text.Clean, type text}}),
    FilterOutEmptyRows = Table.SelectRows(Cleansup2, each ([Custom.1] <> "")),
    ExtractVARandRETURNRows = Table.AddColumn(FilterOutEmptyRows, "VarReturn", each if Text.Upper([Custom.1]) = "RETURN" then "RETURN" else if Text.Start(Text.Upper([Custom.1]),3) = "VAR" then Text.BeforeDelimiter([Custom.1] , "=") else null),
    FillDownVARandRETURNRows = Table.FillDown(ExtractVARandRETURNRows,{"VarReturn"}),
    FilterOutNonRelevantRows = Table.SelectRows(FillDownVARandRETURNRows, each ([VarReturn] <> null) and (Text.Upper([Custom.1]) <> "RETURN")),
    GroupByVARandRETURN = Table.Group(FilterOutNonRelevantRows, {"VarReturn"}, {{"Code", each Text.Combine(_[Custom.1], "#(lf)") }}),
    ExtractVARCode = Table.AddColumn(GroupByVARandRETURN, "VarCode", each Text.AfterDelimiter([Code], "="), type text),
    VarTable = Table.SelectRows(ExtractVARCode, each (Text.Start(Text.Upper([VarReturn]), 4) = "VAR " )),
    AddColSelectionsColumn = Table.FromColumns( Table.ToColumns(VarTable) & {ColumnNames} , Table.ColumnNames(VarTable) & {"ColSelections"}),
    CheckIfColumnNameOrScalar = Table.AddColumn(AddColSelectionsColumn, "JustColumnNames", each Value.Type([ColSelections]) = type text),
    VARDebugCode = Table.AddColumn(
                        CheckIfColumnNameOrScalar, 
                        "Custom", 
                        each if [JustColumnNames] 
                                then fnConcatenateTableValues([VarCode], [ColSelections], TopN, [VarReturn]) 
                                else Text.Replace(
                                        [VarReturn], 
                                        Text.Start([VarReturn],4), 
                                        "VAR Debug") 
                                     & " = " & [Code] & "#(lf)" &  "RETURN" & "#(lf)" & Text.RemoveRange([VarReturn],0,4)
                                    ),
    CleanupCode = Table.TransformColumns(VARDebugCode, {{"VarReturn", each Text.Trim(Text.AfterDelimiter(_, " ")), type text}}),
    CleanupTable = Table.SelectColumns(CleanupCode,{"VarReturn", "Code", "Custom"}),
    CreateTextValue =  "VARDebugMeasure ="   & "#(lf)" & Text.Combine(CleanupTable[Code], "#(lf)") & "#(lf)" & "RETURN" & "#(lf)" 
                                    & "IF(ISBLANK([" & ExtractMeasureName & "]), BLANK(), #(lf) ----- #(lf)" 
                                    & Text.Combine(CleanupTable[Custom], "#(lf) #(lf)") & "RETURN #(lf)" 
                                    & Text.Combine(List.Transform(CleanupTable[VarReturn], each """" &_ & ": "" & IFERROR(Debug" & _ & ", BLANK())"), "#(lf) & UNICHAR(10) & ""----------------------"" & UNICHAR(10) & ") & "#(lf) ----- #(lf))"
                
in
    CreateTextValue
in 
    Source ,
documentation = [
Documentation.Name =  " DAX.VariableDebugger.pq ",
Documentation.Description = " Produces DAX code to debug the variables in a DAX measure. ",
Documentation.LongDescription = " Produces DAX code to debug the variables in a DAX measure. See https://wp.me/p6lgsG-29U for details ",
Documentation.Category = " DAX ",
Documentation.Source = " www.TheBIccountant.com .  https://wp.me/p6lgsG-29U . ",
Documentation.Version = " 1.2: Bugfix for multirow-comments ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-29U . ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))