Forum Discussion
Tricky PowerBI Question
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 🙂
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?
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 🙂
How could I link date so it looks at it by lat 7 Days? Should I add parameters or is this a better way?
