Hello, community,
I have the Power BI data set as below:
| Date | Client | Type | Item | Value |
| 1/12/2022 | client | type 1 | item 1 | 5 |
| 2/12/2022 | client | type 1 | item 1 | 5 |
| 3/12/2022 | client | type 1 | item 1 | 5 |
| 1/12/2022 | client | type 2 | item 2 | 5 |
| 2/12/2022 | client | type 2 | item 2 | 5 |
| 3/12/2022 | client | type 2 | item 2 | 5 |
| 1/12/2022 | client | type 3 | item 1 | 5 |
| 2/12/2022 | client | type 3 | item 1 | 5 |
| 3/12/2022 | client | type 3 | item 1 | 5 |
| 1/12/2022 | client | type 4 | item 2 | 5 |
| 2/12/2022 | client | type 4 | item 2 | 5 |
| 3/12/2022 | client | type 4 | item 2 | 5 |
| 1/12/2022 | client | type 5 | item 3 | 0 |
| 2/12/2022 | client | type 5 | item 3 | 0 |
| 3/12/2022 | client | type 5 | item 3 | 0 |
| 1/12/2022 | client | type 5 | item 4 | 0 |
| 2/12/2022 | client | type 5 | item 4 | 0 |
| 3/12/2022 | client | type 5 | item 4 | 0 |
| 1/12/2022 | client | type 5 | item 5 | 0 |
| 2/12/2022 | client | type 5 | item 5 | 0 |
| 3/12/2022 | client | type 5 | item 5 | 0 |
I am trying to Export a Scheduled CSV file in the SharePoint folder as below:
| DateKey | Client | Type | item 1 | item 2 | item 3 | item 4 | item 5 | Totals |
| 1/12/2022 | client | type 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1/12/2022 | client | type 4 | 0 | 5 | 0 | 0 | 0 | 5 |
| 1/12/2022 | client | type 3 | 5 | 0 | 0 | 0 | 0 | 5 |
| 2/12/2022 | client | type 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2/12/2022 | client | type 4 | 0 | 5 | 0 | 0 | 0 | 5 |
| 2/12/2022 | client | type 3 | 5 | 0 | 0 | 0 | 0 | 5 |
| 3/12/2022 | client | type 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3/12/2022 | client | type 4 | 0 | 5 | 0 | 0 | 0 | 5 |
| 3/12/2022 | client | type 3 | 5 | 0 | 0 | 0 | 0 | 5 |
So basically, I need to filter "Type" column by type3, type 4, type 5 and Pivot "Item" & "value" columns by "Value" and create a new column called "Totals". So the Total column will be Sum(Item).
I tried doing this in different ways by writing the queries against the dataset, but still, I got to do some manual processes.
and also the Date column also giving weird formats like dd-mm-yyyyThh:mm:ss.
Can somebody suggest to me the best and most automated process, please?
Thanks in advance.
