Forum Discussion

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago
Solved

SHA256 in Power Query (and DAX) -- Performance Analysis Tools?

Just to see if I could, I decided to try implementing SHA256 in DAX. I was partly successful but limited lack of recursion, which meant I had to unroll loops manually with a bunch of variables (like ...
Show and Tell
  • lbendlin's avatar
    lbendlin
    4 years ago

    I would flatten it out (make it back into a query) and then run Power Query Diagnostics on the steps that you are interested in.  It will take a long time but eventually it will come back with a couple of diagnostics queries where you can go into detail on the timing of each step.

     

    If you run the diagnostics on the function then you may not get the granularity you'd like. Frankly haven't tried that.

     

    They key here is patience, and a freshly started Power BI Desktop.  Those diagnostics result take ages to appear, and you may think they never come back.  Better step away, do something else and not look at the screen for a while...

     

    Once done you can safely delete the diagnostics query group.

  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi AlexisOlson ,
    hats off to this amazing function - and to provide an example of how useful a buffer can be.
    Never in my live have I been able to speed up a query with a single buffer like this.
    (But maybe this shouldn't be a surprise when triple nested iterators are involved 😉 )

    Just add a buffer here and the results will be returned almost immediately:

     

     

        group_words = Table.Buffer( Table.Group(lists_table, {"chunk", "word"}, {{"w", 
        each List.Accumulate([u], 0, (state, current) => 256 * state + current), Int32.Type}}) ),

     

    BTW: This "trick" is included in my page with Power Query performance tricks here: Speed/Performance aspects – The BIccountant

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

I didn't check if the digest package is available in the Service, but I would do this with R. I am no R expert and adapted code I found online.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NZDBcoIxCIRfZcez8z9Fe+uxPTkeaILKNAkxAfXxS7S9hQDL7nc47HbH/WF34VIUdx0lP+vPC+Pqkn7wPfTecNLH8/9DB1dIn16RtejAFANVtj2StsnJ2HyAsnSZSdoZXCSak3MsgMVn1Qzj2mNZWpIs2ZvBDYW+Qx5sL2lGpXMjUJGr04YvAzepoY0q63GLkup+GZ1oOm14Bj94JDEy0QYvhWrSl/Iakinr0lNSegyDKYzX8KSvAHHKNrwtSXJjyPBw8soqDYP74Au3zCOCx8dNi/c4x2EnkoLnZCQp5Z9QBHKc/CxkaMsQOo0ofGx4fyTuxr4wBgNNiTjFXPIumWxtRIo+VDK3RXGRiqPJS6eVG3o6SRJC5sljdauWZYMWIAkc84+r1213PP4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(digest)#(lf)dataset$sha256 <- sapply(dataset$Text, digest, algo=""sha256"")#(lf)output <- dataset",[dataset=#"Changed Type"]),
    #"""output""" = #"Run R script"{[Name="output"]}[Value]
in
    #"""output"""

 

Pat