Benford's law , Power query

Hi

please paste this code into the advanced editor and follow the steps:

 

let
    MyTable = Table.FromColumns({List.Transform(List.Random(10000), each _ * 10000) } ),
    ExtractLast2Digits = Table.AddColumn(MyTable, "Last2Digits", each Text.End(Text.From([Column1]),2)),
    BenfordTable = 
        Table.AddColumn( 
            #table( {"Last2Digits"}, 
                     List.Transform( {0..99}, 
                     each {Text.PadStart(Text.From(_), 2, "0")})), 
            "BenfordNumber", 
            each 0.01 as number ),
    #"Merged Queries" = Table.NestedJoin(BenfordTable, {"Last2Digits"}, ExtractLast2Digits, {"Last2Digits"}, "MyTable", JoinKind.LeftOuter),
    #"Aggregated MyTable" = Table.AggregateTableColumn(#"Merged Queries", "MyTable", {{"Column1", each List.Count(_) / Table.RowCount(MyTable), "ActualDistribution"}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Aggregated MyTable", "Deviation", each [ActualDistribution] - [BenfordNumber], type number),
    #"Inserted Absolute Value" = Table.AddColumn(#"Inserted Subtraction", "Absolute Deviation", each Number.Abs([Deviation]), type number)
in
    #"Inserted Absolute Value"

 

It will create a table with the Benford-distribution values. 

If you want to apply it to your data, just replace the code in the first step (MyTable) by a reference to your table and make sure that that column with the values to be analyzed is called "Column1". Then you don't have to adjust anything further in the code.