Forum Discussion

NorwaySasquatch's avatar
NorwaySasquatch
Advocate II
5 years ago

Tricky PowerBI Question

Hey all,

 

I have an issue that, as an intermediate user who has googled for hours trying to find the answer to this question and has been unsucessful. 

 

Basically, I have a table with a subset of columns that are solely dedicated to describing errors, and each column represents a different 'bucket' of error. Then, the value within that bucket gives further info that can vary. E.g. one column might be 'Error: Wrong Currency Used" and then a value in one row could be 'Euros not supported' and another row might have 'Yen not supported'. Example here:

 

FilenameWrong Currency UsedInvalid Employee ID
File1'Euros not supported''Too many numbers'
File2 'No ID inputted;
File 3  
File 4'Yen not supported' 

What I'm trying to do is count the number of non-blank rows per column (20-25 columns). Normally this would be easy, however I want to have a visual that shows as such, and allows me to sort the resulting 'Count' column, and apply conditional formatting to the whole 'count' column.

 

Error TypeCount
Wrong Currency Used5
Invalid Employee ID10

 

I've thought about maybe transposing and then 'Group By', but that doesnt allow me to count effectively. Any ideas?

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    NorwaySasquatch 

    Go to Power Query, Paste the below code in a Blank Query, and check the steps. 

    Basically, You select Filename Column and Choose Upivot Other columns
    Now on the Value Column, Filter out Blank
    Group On Error Column and choose Count Rows

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVU0lGKKTUwMDJ3LS3KL1bIyy9RKC4tKMgvKklNgUjAVYTk5yvkJuZVKuSV5ialFhXDpVFQrA7EZCMIH6LIL1/B00UhM6+gtARorjVQPDg/N1XBtagovwiiDp0PN0fBGN0GdPVwlSZw+yJT83B4BZsBaBaa4laDptIMr7uQVZpjNxCbBXA9FniNRlZpiTWIEPKGBjgUxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t, #"Wrong Currency Used" = _t, #"Invalid Employee ID" = _t, #"Error Column 1" = _t, #"Error Column 2" = _t, #"Error Column 3" = _t, #"Error Column 4" = _t, #"Error Column 5" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Filename"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Error"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Error"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     



    • NorwaySasquatch's avatar
      NorwaySasquatch
      Advocate II
      5 years ago

      Hey Fowmy,

      Thanks for the in-depth response. How would this react to filters affecting the original query? I filter based on trailing last 7 days (though I do this manually as of now), when I filter the original table AFTER the query loads would it still update in the derivative table you showed?

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        NorwaySasquatch 

         

        Yes, it will update as the query gets refreshed, the steps will be applied as defined. 

        You mentioned trailing last 7 days, but didn't find any dates involved in your data sample. 

        ________________________

        If my answer was helpful, please consider Accept it as the solution to help the other members find it

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube  LinkedIn