Benford's Law Analysis & Identifying Duplicates

[dsoni]

Hello,

I am an auditor and would like to use Power BI to extract suspicious or unusual transactions from the data. I wanted to run couple things within Power BI; however, I am unable to find appropriate functionalities. 

Is there a way to analyze data through Benford's Law within Power BI. I know it is possible within IDEA (screenshot attached below); however. I am unable to locate similar functionality within Power BI. 

Also, is there a way to identify and extract duplicate transactions from the data. I tried using Edit Queries --> Transform Tab --> Group By --> Advanced. This creates a step and only gives count of duplicated of selected fields; however, it does not provide exact duplicate transaction. I wanted to know if there is any way I could run a function (similar to what IDEA provides in screenshot below - Duplicate Key) which would extract duplicate transactions rather than giving me a count of duplicate fields. 

Thank you. 

[KrisW]

I played with Tableau, I played with IDEA & I played with PBI... this is the best that I can get at this point.

1) Converted amount to string in IDEA (using ABSOLUTE AMT) - @Strip(@Str(AMOUNT,0,2)), export to Excel

2) Append columns for 1st Digit & 1st 2 Digits

3) Create Benford's for each

let
Source = Excel.Workbook(File.Contents("Transaction Detail.xlsx"), null, true),
Database_Sheet = Source{[Item="Database",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Database_Sheet, [PromoteAllScalars=true]),
#"Added 1st Digit" = Table.AddColumn(#"Promoted Headers", "1st Digit", each Text.Middle([Str Absolute Amount],0,1)),
#"Added 1st 2 Digits" = Table.AddColumn(#"Added 1st Digit", "1st 2 Digits", each Text.Middle([Str Absolute Amount],0,2)),
#"Changed Type" = Table.TransformColumnTypes(#"Added 1st 2 Digits",{{"Amount", Currency.Type}, {"1st Digit", Int64.Type}, {"1st 2 Digits", Int64.Type}}),
#"Added Benfords 1st Digit" = Table.AddColumn(#"Changed Type", "Benfords 1st Digit", each Number.Log10([1st Digit] + 1) - Number.Log10([1st Digit])),
#"Added Benfords 1st 2 Digits" = Table.AddColumn(#"Added Benfords 1st Digit", "Benfords 1st 2 Digits", each Number.Log10([1st 2 Digits] + 1) - Number.Log10([1st 2 Digits])),
#"Added Kount" = Table.AddColumn(#"Added Benfords 1st 2 Digits", "Kount", each 1),
#"Changed Type on Benfords" = Table.TransformColumnTypes(#"Added Kount",{{"Benfords 1st Digit", type number}, {"Benfords 1st 2 Digits", type number}, {"Kount", Int64.Type}})
in
#"Changed Type on Benfords"

-------------------------------------------

https://app.powerbi.com/view?r=eyJrIjoiZjQ5NGJiOWMtYWMyMy00MTI3LTg0MGQtM2E1OTZlZGVlNzJlIiwidCI6IjEzM...

[KrisW]

Great challange!

I was just trying to find out if anyone had done Benford's in PBI - have seen it in Tableau. 

re: Exact transactional duplicates, you would need to work as before Duplicate Key Detection became available in IDEA. Create a "character key field" of concatenated (converted to TEXT) values ie: Vendor#, Invoice#, Invoice Date & Invoice Amount. 

To be honest, I would stick with IDEA for the Duplicate Detection as you also have the Fuzzy Duplicate Detection & can save the history to create a script. Then use the ODBC connector available since 10.3 to connect to your IDEA project folder & use Power BI for all your visuals.

I can be reached through the USA IDEA Help Desk. I will be "playing" with Benford's Analysis & PBI once I get back to the office.

[v-piga-msft]

Hi @dsoni,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft]

Hi @dsoni,

You may create R visuals with Benford's Law Analysis in Power BI. Please refer to this blog which may help you.

In addition, it seems that you want to dentify and extract duplicate transactions from the data. You could achieve that in Query Editor.

Please refer to this video Using Power Query to Keep Duplicate Records which shows the details steps.

Best Regards,

Cherry

[smpa01]

"Is there a way to analyze data through Benford's Law within Power BI." - if you know how to quantify that law you can always let PBI do the calculation based on that. I don't think there is a built in "Benford's law" functionality exists in PBI

As far as catching duplications are concerend, please share your sample data, advise on which column to catch the duplicates and I can take a look.

[LivioLanzo]

Benford analysis can be done in Power Query like this: https://blog.crossjoin.co.uk/2015/03/23/benfords-law-and-power-query/

I am sure it could also be done through DAX. Same goes for the duplicates...

[dsoni]

Would same function work within PBI?

[smpa01]

That is for you to find out and if you don't give a try you will never know.