cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Expanding columns not automatically selecting new values during refresh

I have a json file that I update from time to time with new data.

I have set up a query on Power BI to import the json file but first I change it from rows to columns using the unpivot function, then I expand the columns.

 

The problem is that the query doesn't automatically select the new data in the expanded function. I would have to go into each query and manually select all the new values that have been added at that step each time I want to refresh my data.

 

Is there a way to get the query to automatically select all the values that are available when it is expanded, so that I can refresh and get all the new data?

 

I've posted the query below. I basically need to add new dates to the query and they are showing when I expand, but they are not selected. I would have to select them each

 

= Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"2018-11-14", "2018-11-15", "2018-11-16", "2018-11-17", "2018-11-18", "2018-11-19", "2018-11-20", "2018-11-21", "2018-11-22", "2018-11-23", "2018-11-24", "2018-11-25", "2018-11-26", "2018-11-27", "2018-11-28", "2018-11-29", "2018-11-30", "2018-12-01", "2018-12-02", "2018-12-03", "2018-12-04", "2018-12-05", "2018-12-06", "2018-12-07", "2018-12-08", "2018-12-09", "2018-12-10", "2018-12-11", "2018-12-12", "2018-12-13", "2018-12-14", "2018-12-15", "2018-12-16", "2018-12-17", "2018-12-18", "2018-12-19", "2018-12-20", "2018-12-21", "2018-12-22", "2018-12-23", "2018-12-24", "2018-12-25", "2018-12-26", "2018-12-27", "2018-12-28", "2018-12-29", "2018-12-30", "2018-12-31", "2019-01-01", "2019-01-02", "2019-01-03", "2019-01-04", "2019-01-05", "2019-01-06", "2019-01-07", "2019-01-08", "2019-01-09", "2019-01-10", "2019-01-11", "2019-01-12", "2019-01-13", "2019-01-14", "2019-01-15", "2019-01-16", "2019-01-17", "2019-01-18", "2019-01-19", "2019-01-20", "2019-01-21", "2019-01-22", "2019-01-23", "2019-01-24", "2019-01-25", "2019-01-26", "2019-01-27", "2019-01-28", "2019-01-29", "2019-01-30", "2019-01-31", "2019-02-01", "2019-02-02", "2019-02-03", "2019-02-04", "2019-02-05", "2019-02-06", "2019-02-07", "2019-02-08", "2019-02-09", "2019-02-10", "2019-02-11", "2019-02-12", "2019-02-13", "2019-02-14", "2019-02-15", "2019-02-16", "2019-02-17", "2019-02-18", "2019-02-19", "2019-02-20", "2019-02-21", "2019-02-22", "2019-02-23", "2019-02-24", "2019-02-25", "2019-02-26", "2019-02-27", "2019-02-28", "2019-03-01", "2019-03-02", "2019-03-03", "2019-03-04", "2019-03-05", "2019-03-06", "2019-03-07", "2019-03-08"}, {"2018-11-14", "2018-11-15", "2018-11-16", "2018-11-17", "2018-11-18", "2018-11-19", "2018-11-20", "2018-11-21", "2018-11-22", "2018-11-23", "2018-11-24", "2018-11-25", "2018-11-26", "2018-11-27", "2018-11-28", "2018-11-29", "2018-11-30", "2018-12-01", "2018-12-02", "2018-12-03", "2018-12-04", "2018-12-05", "2018-12-06", "2018-12-07", "2018-12-08", "2018-12-09", "2018-12-10", "2018-12-11", "2018-12-12", "2018-12-13", "2018-12-14", "2018-12-15", "2018-12-16", "2018-12-17", "2018-12-18", "2018-12-19", "2018-12-20", "2018-12-21", "2018-12-22", "2018-12-23", "2018-12-24", "2018-12-25", "2018-12-26", "2018-12-27", "2018-12-28", "2018-12-29", "2018-12-30", "2018-12-31", "2019-01-01", "2019-01-02", "2019-01-03", "2019-01-04", "2019-01-05", "2019-01-06", "2019-01-07", "2019-01-08", "2019-01-09", "2019-01-10", "2019-01-11", "2019-01-12", "2019-01-13", "2019-01-14", "2019-01-15", "2019-01-16", "2019-01-17", "2019-01-18", "2019-01-19", "2019-01-20", "2019-01-21", "2019-01-22", "2019-01-23", "2019-01-24", "2019-01-25", "2019-01-26", "2019-01-27", "2019-01-28", "2019-01-29", "2019-01-30", "2019-01-31", "2019-02-01", "2019-02-02", "2019-02-03", "2019-02-04", "2019-02-05", "2019-02-06", "2019-02-07", "2019-02-08", "2019-02-09", "2019-02-10", "2019-02-11", "2019-02-12", "2019-02-13", "2019-02-14", "2019-02-15", "2019-02-16", "2019-02-17", "2019-02-18", "2019-02-19", "2019-02-20", "2019-02-21", "2019-02-22", "2019-02-23", "2019-02-24", "2019-02-25", "2019-02-26", "2019-02-27", "2019-02-28", "2019-03-01", "2019-03-02", "2019-03-03", "2019-03-04", "2019-03-05", "2019-03-06", "2019-03-07", "2019-03-08"})

 

Only the dates listed above are selected, the other dates remain unselected each time

2 REPLIES 2
Microsoft
Microsoft

Hi @Anonymous ,

 

How is the table structure like before unpivoting? What is your desired output after unpivoting? Please illustrate with sample data and screenshot. How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yulgu-msft ,

 

Thanks for looking at this.

 

Here is the screen grab showing which dates I need to accept. The dates that are not selected, require me to manually select them each time, before I can refresh to get the new data.

 

Need to select new datesNeed to select new dates

Before I get to this point I have the data nested in a JSON file with a structure that looks like this:

 

{Business name:{keywords:{landing pages:{dates:{"value1":23,"value2":16,"etc":etc}}}}}

 

It's not really important to understand json as that is not the problem. Basically the nested data results in me needing to unpack it using the unpivot function whic changes from rows to columns. 

 

Above I posted what the actual dax query looks like. I am hoping that someone could help to point me in the right direction for updating the dax code so that it automatically adds the new date columns to the data.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors