I've got a query which gets data from multiple spreadsheets tracking hours worked by personnel.
It is used to calculate the utilisation upcoming and historic for staff members for a given week of work.
The preferred format of this data is Name Date1 Date2 Date3 ...
Me 100% 95% 92%
You 100% 20% 50%
Them 1% 50% 100%
However, as I understand it, currently I would have to set up the table in the report and individually select the name and each of the date columns. I want to do this dynamically so that as dates are added I don't have to manually add them to the table.
As tested, if i delete the date1, date2, date3 columns from my original datasource and add date4, date5, date6 columns, then click on refresh button in the power bi desktop, it show an error that says:
Expression.Error: The column '9/1/2018' of the table wasn't found. Details: 9/1/2018
"9/1/2018" is the header of date1 column.
if i select the date1, date2, date3 columns in Edit Queries, Unpivot these columns, then when i add date4, date5, date6 columns to my original table and keep the date1, date2, date3 columns, after refreshing from power bi desktop, it shows the date1, date2, date3 columns together with the date1, date2, date3 columns in a matrix visual.