Comparing data sets

[em101]

Hi there 

I have been trying to compare two data sets (for example between the month of November and December) in Power BI.  

I have included Dropbox links below to basic data that I am trying to use in Power BI. 

Essentially, I'm hoping to show there was 1 risk with the rating of 'High' in November but 2 risks with a rating of 'High' in December. Ideally you could also filter to show many Medium or Low risks there were by month. 

November sample data: 

https://www.dropbox.com/scl/fi/ci7lp22ryjp5vu50xdaim/Risk-data-Nov-2023.xlsx?rlkey=bzianw7mis0oe90sm...

December sample data: 

https://www.dropbox.com/scl/fi/l6k2taqwiiufzyj3hrg02/Risk-data-Dec-2023.xlsx?rlkey=ohstd4ie99e0hhp1f...

I've tried experimenting based on some other posts but I'm a bit lost. If somebody could respond with the specific steps you need to use in Power Query it would be greatly appreciated. 

Thank you 

em101

[em101]

Hi @lbendlin 

Thanks for your reply, but I'm new to PowerBI and a bit confused still. 

If you can explain the steps in a bit more plain English that would be great, eg you first load the two files into Power Query, then Filter Rows by doing XYZ... 

If possible, can you provide a sample Power BI file showing the implemented steps you documented above? 

Many thanks 

@em101 

[lbendlin]

I can provide a sample pbix but you will have to adjust the Source part and point it to the folder where you have the files.

[em101]

Hi @lbendlin 

Many thanks for your help, that seemed to work. 

I was just wondering do you know if instead of using the method you suggested where you point to a particular Source location for the 2 files, is it possible to add both the Risk data Nov 2023 and Risk data Dec 2023 as 2 seperate Queries into Power Query and then use the Merge Queries option on their Residual Risk Rating? If you can let me know the steps if you take that approach that would be appreciated. 

Many thanks 

[lbendlin]

Having separate sources is not a sustainable approach, I would recommend against doing that.

[lbendlin]

let
    Source = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Risk data") and Text.EndsWith([Name], ".xlsx")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.PromoteHeaders(Excel.Workbook([Content]){0}[Data], [PromoteAllScalars=true])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Residual Risk Rating", "Risk"}, {"Residual Risk Rating", "Risk"})
in
    #"Expanded Custom"