Forum Discussion

em101's avatar
em101
Frequent Visitor
2 years ago

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=bzianw7mis0oe90sm0s5zpx34&dl=0

 

December sample data: 

https://www.dropbox.com/scl/fi/l6k2taqwiiufzyj3hrg02/Risk-data-Dec-2023.xlsx?rlkey=ohstd4ie99e0hhp1f06piowzy&dl=0

 

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

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago
    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"
  • em101's avatar
    em101
    Frequent Visitor
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        em101
        Frequent Visitor
        2 years ago

        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