How to handle columns that change each refresh?

[jlankford]

Walmart gives data on a week by week basis. They name columns YEARWEEK, so last week was 201736.

Data gets refreshed each week, and we call the previous 52 weeks. 

This poses two issues each refresh:

1.) The oldest week is no longer there, and there's now a new oldest week. "201636" will not be a column in the next refresh

2.) The newest week (201737) will show up.

So what are my options outside of manually adding them to the tables, and manually rewriting all my measures that use these columns?

Thanks

[Anonymous · Accepted solution]

I'm assuming you're going to combine these columns into a single set with the column names as descriptors.

If you use the Table.UnpivotOtherColumns function, it should just work regardless of column names. You'd have something like this:

#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")

Whereas "Attribute" would be the new column title for your previous column names (dates) and the value would be whatever is underneath that. If you have more than one row per column, I'd have to see a sample of your data to investigate further.

[jlankford]

Thank you - I wasn't able to try this as ultimately, I found a better way to query my data from the source that adds a "date" column with all date values rather than a date column for each separate day or week (365 additional columns). 

I do appreciate your help, and I'm glad I can reference this later. Unpivoting hadn't occurred to me. Makes a lot of sense.