Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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.
Is there a way of doing this?
Thanks for your help.
if i understand u
try to use unpivot columns in query editor
Me date1 100%
Me date2 95%
Me date 3 92%
You date1 100%
Hi,
Thanks for your response.
What I'm after is a way to paste the entire table that you can see in the query editor in a Power BI visual.
In other words, if any of the columns in the query editor change, then the Power BI table will also update without requiring the user to select the appropriate columns.
So, if the query editor looks like this:
Name Date1 Date2 Date3 ...
Me 100% 95% 92%
You 100% 20% 50%
Them 1% 50% 100%
Then I want the visual to look like this:
Name Date1 Date2 Date3 ...
Me 100% 95% 92%
You 100% 20% 50%
Them 1% 50% 100%
But should the data in the query editor change to this, where the dates have changed:
Name Date4 Date5 Date6 ...
Me 100% 95% 92%
You 100% 20% 50%
Them 1% 50% 100%
I want the table in the Power BI Report to automatically update to this without the user having to change the columns from Name, Date1, Date2, Date3 to Name, Date4, Date5, Date6:
Name Date4 Date5 Date6 ...
Me 100% 95% 92%
You 100% 20% 50%
Them 1% 50% 100%
I hope that makes more sense.
Hi @JontySchulz
columns change from Name, Date1, Date2, Date3 to Name, Date4, Date5, Date6 in query editor, are the columns Name, Date1, Date2, Date3 replaced by columns Name, Date4, Date5, Date6?
Or add columns Name, Date4, Date5, Date6 to the original table including the columns Name, Date1, Date2, Date3.
Best Regards
Maggie
Hello Maggie,
I'm looking for the date columns being replaced in the example above.
If the column no longer exists in the query, then I don't want it to exist in the table visual.
Cheers,
Jonty
Hi
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.
Best Regrads
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |