Is there a way to present the entire query (all columns and all rows) in a table visual?

[JontySchulz]

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.

[petrovnikitamai]

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% 

[JontySchulz]

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.

[v-juanli-msft]

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 

[JontySchulz]

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

[v-juanli-msft]

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