Count number of Power Query applied steps in Power BI

[thod]

Hi,

I have gathered metadata from our Power BI tenant through the scanner API.

In this I have "let" expressions (power query advanced editor) from all semantic models and I want to count the number of applied steps in used for each table in these semantic models. Some examples of "let" expressions are:

No. 1 - One applied step:
 let
Source = Sql.Database(#"Server Prod - Dataplatform", #"Database Prod - SharedDimensions", [Query="SELECT [DW_SK_Brand]#(lf) ,[BrandId]#(lf) ,[Brand]#(lf) ,[BrandBeskrivelse]#(lf) FROM [DSO_SharedDimensions].[all].[DimBrand]"])
in
Source

No 2. - Two applied steps:
let
Source = Sql.Database(#"Server Prod - Dataplatform", #"Database Prod - SharedDimensions"),
dpa_FctBeregninger = Source{[Schema="all",Item="FctBeregninger"]}[Data]
in
dpa_FctBeregninger

No 3 - Three applied steps:
let
Source = Sql.Database(#"Server Prod - Dataplatform", #"Database Prod - SharedDimensions", [Query="SELECT [DW_SK_Produkt]#(lf) ,[ProduktID]#(lf) ,Produkt#(lf) ,Produktgruppe#(lf) ,[ProduktBeskrivelse]#(lf) ,[ProduktBeskrivelseENG]#(lf) FROM [DSO_SharedDimensions].[all].[DimProdukt]"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([ProduktID] = 1000000 or [ProduktID] = 1000001)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DW_SK_Produkt", Int64.Type}})
in
#"Changed Type"

So two sum up, I want a measure that calculates how many applied steps there is for each "let" expression in the dataset.

Br.,
Thomas

[Greg_Deckler · Accepted solution]

@thod Simple enough. PBIX below sig.

Measure = 
    VAR __Text = MAX('Table'[Column1])
    VAR __Length = LEN(__Text)
    VAR __Replace = SUBSTITUTE( __Text, "," & UNICHAR(10), "" )
    VAR __NewLength = LEN(__Replace)
    VAR __Diff = __Length - __NewLength
    VAR __Result = DIVIDE( __Diff, 2 ) + 1
RETURN
    __Result

[thod]

Hi @Greg_Deckler ,
Thank you very much. I had to modify your measure a tiny bit, as it for some reason included a lot of tables that is not a part of the data set with the result of one.
So the modified measure that works is:

measure =
VAR __Text =
    MAX ( 'Table'[Column1] )
VAR __Length =
    LEN ( __Text )
VAR __Replace =
    SUBSTITUTE ( __Text, "," & UNICHAR ( 10 ), "" )
VAR __NewLength =
    LEN ( __Replace )
VAR __Diff = __Length - __NewLength
VAR __Result =
    IF ( ISBLANK ( __Length ), BLANK (), DIVIDE ( __Diff, 2 ) + 1 )
RETURN
    __Result

Br,
thod