Tricky PowerBI Question

[NorwaySasquatch]

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:

Filename	Wrong Currency Used	Invalid 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 Type	Count
Wrong Currency Used	5
Invalid Employee ID	10

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

[Fowmy]

@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]

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]

@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

[NorwaySasquatch]

How could I link date so it looks at it by lat 7 Days? Should I add parameters or is this a better way?