Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
em101
Frequent Visitor

Comparing data sets

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

5 REPLIES 5
em101
Frequent Visitor

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 

 

 

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.

 

 

 

 

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 

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

lbendlin
Super User
Super User

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors