Expanding columns not automatically selecting new values during refresh

[Anonymous]

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

[v-yulgu-msft]

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

[Anonymous]

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 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.