cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JontySchulz Frequent Visitor
Frequent Visitor

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

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.

5 REPLIES 5

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

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% 

11.jpg

JontySchulz Frequent Visitor
Frequent Visitor

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

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.

 

Community Support Team
Community Support Team

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

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 Frequent Visitor
Frequent Visitor

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

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

Community Support Team
Community Support Team

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

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.

3.png4.png

 

Best Regrads

Maggie

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)