Data Model Missing a Column Automatically Created in Power Query

[Anonymous]

I am running a refresh of Power Query in excel 2013. The Query pulls from a source excel file. The source excel file contains multiple columns, one of which is a Year columns (about 700k rows, listing years 2013 - 2019). We received new 2019 information and Query is built to list distinct Years from source data as new columns, which i can see it does in the query editor preview pane.

My problem is the data model does not show this newly created 2019 column. I can't manage table properties in  the data model, as an error message notes the data was imported using Query and therefore can only be changed in Power Query. The 2019 column is also a selected viewing column in the Query Editor. Any thoughts?

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

Do you click on "close&&apply" before close "Power Query" editor?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey Maggie -

Thanks for your response. When I import the file path for the new excel file, I refresh the query which then loads the updated data (and I can see 2019 column is populated in the query editor, but not the data model). I did select close and load after updating the query just to be sure, but this just closed out the editor window and nothing really changed.

To be thorough, i also imported the updated file path, and tried to "close & load", which had the same effect of just closing out the query editor window and not impacting anything else.

On all previous data loads, refreshing the query from the "show pane" dialogue box has worked to refresh the data model.

[kcantor]

@Anonymous 

Can you share your query here and maybe a snip or sample of the data? It can be difficult to diagnose a missing query without seeing the "M" behind it. 

In query editor, select Advance Editor and copy and past here for use to see.

[Anonymous]

This is the relevant portion of the Query M, which calls out the treatment of the "Year" column:

let
    #"Removed Columns" = Table.RemoveColumns(Source,{"YearQtr"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Year]), "Year", "Amount_$", List.Sum)
in
    #"Pivoted Column"

I included a snip of how the "Year" column is presentedin the excel file data. 2019 is the first year where the Data model does not show an additional column.Years are in columns in the source data file.

[v-juanli-msft]

Hi @Anonymous 

As tested, if i click on "refresh preview" in Edit queries,

then "close&&apply", return back to Data Model view, the data doesn't update as expected.

This way, you need to click on "refresh" under Home menu.

If i click on "refresh all" in Edit queries,

then "close&&apply", return back to Data Model view, the data update as expected.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.