Forum Discussion
Data Entry Spreadsheet to Appealing Visual Dashboard
Hello,
I am a relative novice when it comes to PowerBI, but I've found many posts on this forum to be really helpful. That said, I'm a bit stuck with something and struggling to find the words to describe the problem in a search box!
We have a spreadsheet that users manually populate in the morning. The intention is for this to drive a BI Dashboard for visualisation and review. Eventually, the data will be automatically populated.
I need the dashboard to show information in the following format, for a single day:
TYPE | Required | Available | Limited | Error |
Sector 390 | 49 | 50 | 129 | 138 |
Sector 221 | 13 | 00 | 112, 114 | 202 |
Sector 805 | 20 | 19 | 001 |
|
Sector 807 | 10 | 12 | 005 |
|
However, the data currently looks like this:
| Mon 07 Aug 2023 | Tue 08 Aug 2023 | Wed 09 Aug 2023 | |
| Sector 390 | |||
| Required | 50 | 50 | 50 |
| Available | 48 | 40 | 52 |
| Limited | 129 | - | 129 |
| Error | 138 | 138, 145 | - |
| Sector 221 | |||
| Required | 15 | 15 | 15 |
| Available | 17 | 17 | 17 |
| Limited | 112, 114 | - | 112 |
| Error | 202 | 202 | - |
| Sector 805 | |||
| Required | 20 | 20 | 20 |
| Available | 19 | 10 | 21 |
| Limited | 001 | 001 | - |
| Sector 807 | |||
| Required | 10 | 10 | 10 |
| Available | 12 | 9 | 10 |
| Limited | 005 | - | 001 |
I'm struggling with reformatting the data in PowerBI. So far, I have transposed the table in Power Query and promoted Row 1 to be headers, this allows the existing Slicer on the report to filter by date row. For convenience, I had also thought about adding a new column before the data to provide unique labels, e.g. "Sector 807 - Required". Whatever visual I try to use (Table, Matrix, Multi-row Card) I just cannot get the data to look right which makes me think further manipulation is required pre-visual.
"Dummies Guide" advice would be appreciated. Thank you in advance.