Kharkiv Power BI User Group

Hi guys,

On the way of full 1:1 migration of Vertipaq Analyzer from Excel to Power BI I have updated Power BI version, where applied DAX Formatter API to the data which contains DAX code. Just to remind that this tool retrieves meta data from PBIX/Tabular model (tables, columns, DAX formulas, what is actually need to formatted) and do analytics of it as par of model optimization process.

What is DAX Formatter? DAX Formatter is a free tool by SQLBI that transform your raw DAX formulas into clean, beautiful and readable code. The syntax rules used improves the readability of the expressions, read this article to learn more: Rules for DAX code formatting.

And formatting is integrated into initial version of Vertipaq Analyzer in Excel via VBA. See my previous article about it - Vertipaq Analyzer.
The link to the new version you will see on the bottom. I will say a few words about challenges happened and new limits found first.

In common I've done this by implementing a custom function in PowerQuery.
If your DAX formulas which you want to format via API are smaller then 2000 symbols approximate then you can use GET request otherwise POST. Here is a code of both versions:

GET:

(DAXCode as text, Region as text) =>
let
    DAXCodeFull = "= " & DAXCode,
    DAXCodeURIEncoded = Text.Middle(Uri.BuildQueryString([DAXExpr = DAXCodeFull]),8),
    URIPart = "?r=US&embed=1&app=VertiPaqAnalyzer&version=1.91&fx=" & DAXCodeURIEncoded,
    Source = Web.Page(Web.Contents("http://www.daxformatter.com/" & URIPart)),
    Data = Source{0}[Data]{0}[Children]{2}[Children]{0}[Children]{0}[Children],
    #"Expanded Children" = Table.ExpandTableColumn(Data, "Children", {"Text"}, {"Children.Text"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Children", "Custom", each 
                                                                            if [Name] = "BR" 
                                                                                then "#(lf)" 
                                                                                    else 
                                                                                        if [Children.Text] is null 
                                                                                            then [Text] 
                                                                                            else [Children.Text]
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Children.Text", "Text"}),
    Output = Lines.ToText(#"Removed Columns"[Custom],"")
in
    if Text.Length(DAXCode) > 0 then Output else DAXCode

POST:

(DAXCode as text, Region as text) =>
let
    DAXCodeFull = "= " & DAXCode,
    DAXCodeURIEncoded = Text.Middle(Uri.BuildQueryString([DAXExpr = DAXCodeFull]),8),
    Body = "r=" & Region & "&embed=1&app=VertiPaqAnalyzer&version=1.91&fx=" & DAXCodeURIEncoded,
    Source = Web.Page(Text.FromBinary(Web.Contents("http://www.daxformatter.com/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Body)]))),
    Data = Source{0}[Data]{0}[Children]{1}[Children],
    #"Expanded Children" = Table.ExpandTableColumn(Data, "Children", {"Name", "Children", "Text"}, {"Children.Name", "Children.Children", "Children.Text"}),
    #"Children Children" = #"Expanded Children"{0}[Children.Children],
    #"Removed Columns" = Table.RemoveColumns(#"Children Children",{"Kind"}),
    #"Expanded Children1" = Table.ExpandTableColumn(#"Removed Columns", "Children", {"Text"}, {"Children.Text"}),
    AddColumn = Table.AddColumn(#"Expanded Children1", "Custom", each 
                                                                        if  [Name] = "BR" 
                                                                            then "#(lf)" 
                                                                            else
                                                                                if  [Children.Text] is null 
                                                                                    then [Text] 
                                                                                    else [Children.Text]
    ),
    RemoveColumns = Table.RemoveColumns(AddColumn,{"Name", "Children.Text", "Text"}),
    Output = Lines.ToText(RemoveColumns[Custom],"")
in
    if Text.Length(DAXCode) > 0 then Output else DAXCode

P.S. Using Content in Web.Contents converts GET request to POST. See documentation from Microsoft - https://docs.microsoft.com/en-us/powerquery-m/web-contents

During implementation of a POST version function I found a limitation that a result of Web.Contents function can't be a parameter of Web.Page function if you add Content parameter to Headers of Web.Contents and it ends with en error

For instance, based on my examples, this code works:

Source = Web.Page(Web.Contents("http://www.daxformatter.com/" & URIPart))

But next one no:

Source = Web.Page(Web.Contents("http://www.daxformatter.com/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Body)]))

The response from Microsoft support team on the issue:

"Product team has confirmed that POST command cannot be used with Web.Page"

But it is easy to fix, just put Web.Contents to Text.FromBinary first and then to Web.Page:

Source = Web.Page(Text.FromBinary(Web.Contents("http://www.daxformatter.com/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Body)])))

And finally, let see the difference between v1 and v2 of Vertipaq Analyzer in Power BI:
vs

The second version has a column Expression, which has data with DAX formulas with well structured code.

​Here is a link at this portal to the Power BI version of Vertipaq Analyzer 1.92-1.6-2.3.

I'm happy to see comments (good and even bad ones) on this result.




------------------------------
Ruslan Zolotukhin
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader
------------------------------