Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
03-31-2019 14:26 PM - last edited 05-24-2020 03:40 AM
Here is a PowerBI Document designed to show differences in data from two similar data sets
It’s a common, but difficult requirement to check data sets against each other. To solve this problem I've build some PowerBI documents that can be used to compare a SQL or file based data sets.
All files and samples on Github
https://github.com/stretcharm/PBIDataComparison
Set Parameters DataSetName, FilePath1, FilePath2, File1, File2, FileFormat1 and FileFormat2
File Formats supported are Excel, Csv or Text Files should have headers
If the file is Excel Specify ExcelSheet1 & ExcelSheet2
The files can be the same or different, but should provide the same date range and columns. If the column names are different alias one query so they have the same name.
Data Set needs to have 2 fields
So either make sure your Data has a Columns named RecordKey & RecordDate or Rename/Create Columns in the PowerBI QueryEditor for Quer1Data and Query2Data You can also adjust file columns in the QueryData1 & QueryData1
Set Parameters DataSetName, Server1, Server2, Database1, Database2
Set the Select Query for the Data you want to Compare.
Query1 and Query2 can be the same or Different, but should Provide the same date range and columns. If the Column names are different alias one query so they have the same name. If they are the same you can Simple set let Source = Query1 in Source in Query2
Data Set needs to have 2 fields
e.g.
SELECT InvoiceID as RecordKey,
InvoiceDate as RecordDate
Quantity,
Amount
FROM Invoice
Set Parameters DataSetName, Server1, Server2, Database1, Database2
Set the Select Query for the Data you want to compare.
Query1 and Query2 can be the same or Different, but should Provide the same date range and columns. If the Column names are different alias one query so they have the same name. If they are the same you can Simple set let Source = Query1 in Source in Query2
Data Set needs to have 2 fields
SELECT DataID as RecordKey,
RecordedData as RecordDate
IntegrityName as Attribute,
IntegrityValue as [Value]
FROM IntegrityData
Pages are filtered using the new Filter Panes
Various Drillthroughs or Tooltips are available depending on the visual
Once loaded you have these Pages or Sets of Pages
Keep data sets small to start with then expand as large data sets take a long time to load. Avoid timestamps and Surrogate Keys as they can result if false differences
eyJrIjoiMjAxYzA3MWUtYmQwMS00MDk4LThlZjQtNWUyNzAwNGI0NDY2IiwidCI6ImEwYTcyYzIzLTdlMWEtNGYxOC05NDU4LTlhNzUyYTEzMTg1NCJ9