Hi. I'm looking for the simplest way to analyze data stored in a tabular format in an excel sheet and make comparisons (text as well as value fields) using dates as columns.
To clarify, I have data for five days spanning over 20 different columns stored in an excel sheet. For each day, the date is stored in the date column (a part of 20 columns).
Consider something like this
Raw Data
Desired output.
Desired format
(Assume that the date columns are merged for owner and amount fields)
In the table above, as you can see that the data is automatically arranged in a columnar format based on the available dates. And all of the data is referenced against the ID field to create a comparison table.
Also, the above
Current efforts:
The reason I'm persistent in using PIVOT table is that it helps me select the columns or rearrange the rows, or even add filters very quickly.
If anyone could point out the right direction, I would be immensely grateful to them.
To clarify, I have data for five days spanning over 20 different columns stored in an excel sheet. For each day, the date is stored in the date column (a part of 20 columns).
Consider something like this
Raw Data
Desired output.
| Date | ID | Owner | Amount |
01-05-2021 | A01 | ABC | 2,000 |
01-05-2021 | B01 | DEF | 10,000 |
01-05-2021 | C01 | EFG | 6,000 |
02-05-2021 | A01 | ABC | 3,000 |
02-05-2021 | C01 | CDE | 5,000 |
03-05-2021 | A01 | XYZ | 7,000 |
03-05-2021 | C01 | CXZ | 8,000 |
03-05-2021 | D01 | QRT | 4,000 |
03-05-2021 | E01 | TSU | 6,000 |
Desired format
01-05-2021 | 02-05-2021 | 03-05-2021 | ||||
| ID | Owner | Amount | Owner | Amount | Owner | Amount |
| A01 | ABC | 2,000 | ABC | 3,000 | XYZ | 7,000 |
| B01 | DEF | 10,000 | ||||
| C01 | EFG | 6,000 | CDE | 5,000 | CXZ | 8,000 |
| D01 | QRT | 4,000 | ||||
| E01 | TSU | 6,000 |
(Assume that the date columns are merged for owner and amount fields)
In the table above, as you can see that the data is automatically arranged in a columnar format based on the available dates. And all of the data is referenced against the ID field to create a comparison table.
Also, the above
Current efforts:
- Using PIVOT Table, I can partially generate this format by Putting ID as Rows, Dates as Columns, and Amounts as values. However, when it comes to text fields pivot fails to perform and returns count of values instead of the actual tax. I am aware of using Data Model and creating a DAX-based Calc field (CONCATENATEX) to return the text values. But, as I said above source data can have more than 20 columns, which could be a big pain to create dax fields for every column. Becomes more time-consuming.
- An alternative is to create a set of unique values extracted from ID, and then using VLOOKUP replicate all of the fields for all the different dates as columns. e.g. Owner_01-05-2021, Owner_02-05-2021,Owner_03-05-2021. However, this is also very time-consuming, and needless to mention this will 100 columns for 5 dates, which honestly becomes unnecessarily vast.
- In the given example we have only considered ID as a lookup field, however, there could be multiple lookup fields (or Pivot Row items), that would be required. This does not seem to be manageable using method 2.
The reason I'm persistent in using PIVOT table is that it helps me select the columns or rearrange the rows, or even add filters very quickly.
If anyone could point out the right direction, I would be immensely grateful to them.
