Power Query refresh removes manually entered data

[nryan]

Hello all,

I've used Power Query to build a table and I want to enter data into the table after the fact. When I do this however and then refresh the table all the data goes away. The conditional formatting and data validation remain but no data.

Here's some more info that may or may not matter:
The table I've set up in PQ is somewhat complex. It's a combination of 2 tables that I transformed quite a bit before appending one to the other. The first table is TrainingMatrix. The second is PersonnelList. Here's what it looks like:

TrainingMatrix Table:                                                       PersonnelList Table:
        A            B              C             D                                   A            B
1     Doc ID     Category X     Category Y     Category Z                    1      Name        Title
2      001                         Yes            Yes                        2       Tuck         Eng
3      002           Yes                          Yes                        3       Dale         Tech
4      003           Yes           Yes                                       4       Ned        Neighbor

PQ transforms and appends tables into this table:
        A            B           C       D        E
1     Doc ID      Category     Tuck     Dale     Ned
2      001           Y
3      001           Z
4      002           X          (This stuff will be
5      002           Z          filled in manually.)
6      003           X
7      003           Y

It's great because I can update the two source tables (add new rows/columns, add "Yes" to categories) and the Power Query table updates to match. I don't know if what I'm trying to do is possible. I hope so. I've been looking for solutions for a few hours now with no luck.

Thank you in advance. I've used this forum for VBA questions before and the responses have always helped me.
Cheers!

 

[MarcelBeug]

In Excel/Power Query it is possible to use so called "self referencing queries", meaning that you use the output table from a query as input for the same query.

If you merge that with the data from the other tables, then your manually entered data will be kept (if you do it correctly...).

The principle is explained in this video, where comments are added to data from SQL server and these comments are kept when the SQL data is refreshed:

https://youtu.be/duNYHfvP_8U?list=PLmajzIMNl6yH7MvMLmlgGUW5dOsKg74mQ

 

[nryan]

Thanks for the link MarcelBeug.

I've been trying this method for a while, tweaking things here and there, and I can't get it to work for my application. It looks good for adding extra columns but not new data to existing columns. I think this may be the right track and I'll keep trying, but so far no good.

 

[MarcelBeug]

This is what I made of query PQTable:

let
    Source = Excel.CurrentWorkbook(){[Name="PQTable"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TrainingMatrix, {"Doc ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Category"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Category", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Text After Delimiter",{"Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Doc ID", "Category"},Source,{"Doc ID", "Category"},"Removed Columns",JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", List.Skip(Table.ColumnNames(Source),2)),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Removed Columns",{"Doc ID", "Category"}&PersonnelList[Name], MissingField.UseNull)
in
    #"Removed Other Columns"

 

[nryan]

Hi Marcel,

I remade the PQ table in attempt to follow your code. I even named it "PQTable".

let
    Source = Excel.CurrentWorkbook(){[Name="TrainingMatrix"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", type text}, {"Document Name", type text}, {"Major Rev", Int64.Type}, {"[Category X]", type text}, {"[Category Y]", type text}, {"[Category Z]", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Document ID", "Document Name", "Major Rev"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Category"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Document ID"},PersonnelList,{"Tuck"},"PersonnelList",JoinKind.LeftOuter),
    #"Expanded PersonnelList" = Table.ExpandTableColumn(#"Merged Queries", "PersonnelList", {"Tuck", "Dale", "Ned", "[Name]", "[Name]", "[Name]", "[Name]"})
in
    #"Expanded PersonnelList"

This is before any data is entered. It creates this table, which again I named "PQTable":

        A            B           C       D        E
1     Doc ID      Category     Tuck     Dale     Ned
2      001           Y
3      001           Z
4      002           X          (This stuff will be
5      002           Z          filled in manually.)
6      003           X
7      003           Y

Note: In the code you may notice 2 extra columns ("Document Name" and "Major Rev"). This is because I left them out of the table I drew above, to simplify it. Anyway...

This is your code. I highlighted the things that caught my attention:

let
    Source = Excel.CurrentWorkbook(){[Name=[COLOR=#ff0000]"PQTable"[/COLOR]]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TrainingMatrix, {"Doc ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Category"}}),
    [COLOR=#ff0000]#"Extracted Text After Delimiter"[/COLOR] = Table.TransformColumns(#"Renamed Columns", {{"Category", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Text After Delimiter",{"Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Doc ID", "Category"},Source,{"Doc ID", "Category"},"Removed Columns",JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", List.Skip(Table.ColumnNames(Source),2)),
    [COLOR=#ff0000]#"Removed Other Columns" = Table.SelectColumns(#"Expanded Removed Columns",{"Doc ID", "Category"}&PersonnelList[Name], MissingField.UseNull)
in
    #"Removed Other Columns"[/COLOR]

It looks like a self-referencing query where the source is changed from "TrainingMatrix" to "PQTable". I'm just not sure what the "Removed Other Columns" refers to. Maybe you can help me on that. The "Extracted Text After Delimiter" just looks like a formatting thing you did; is it important for me to pay attention to?

Thanks.

 

[MarcelBeug]

"Removed Other Columns" selects the column of the new table, being "Doc Id", "Category" and all names from table PersonnelList.
The last parameter, MissingField.UseNull means that a column with null values will be added for any new name in PersonnelList
You can try and see the effect when you add a name to the PersonnelList and refresh the queries.

"Extracted Text After Delimiter" converts "Category X", "Category Y", etc. - from your original column headers, after unpivoting these are row values - to "X", "Y", etc.
Indeed, it is jut formatting, so up to you to decide if it is important or not.

Notice that the Source step imports PQTable, the following steps transform TrainingMatrix until step "Merged Queries" where the 2 tables are merged.

 

[nryan]

I'm having trouble following. I can't seem to get your code to work. There are all sorts of errors. I may be missing something basic. I'm very new to Power Query.

Here's what I did. In a new Excel workbook I created the"TrainingMatrix" and "PersonnelList" tables exactly as I drew them:

TrainingMatrix Table:                                                       PersonnelList Table:
        A            B              C             D                                   A            B
1     Doc ID     Category X     Category Y     Category Z                    1      Name        Title
2      001                         Yes            Yes                        2       Tuck         Eng
3      002           Yes                          Yes                        3       Dale         Tech
4      003           Yes           Yes                                       4       Ned        Neighbor

I did this because my real TrainingMatrix table looks a bit different than the one above (real one has 2 extra columns and different column names). The one above is simplified. The PersonnelList table is the same, just with more rows and different person's names.

I then made connections to these 2 tables in Power Query by choosing "Close & Load To..." and choosing "Only Create Connection". Not sure this is needed.

I then clicked on the TrainingMatrix table and in the Power Query tab chose "From Table/Range" to make another table. I renamed it "PQTable" and hit "Close & Load".

I then made sure the name was "PQTable", selected it, chose "Edit", and went into the Advanced Editor and copy/pasted your code.

Here's the error I got:
Expression.Error: The column 'Category' of the table wasn't found.
Details:
Category

You posted a link earlier to a guy showing how to make self referencing queries. Am I supposed to combine the solution from that link into somehow with the code you proposed?

 

[nryan]

I'll be more specific. Here's my code for making "PQTable"

let
    Source = Excel.CurrentWorkbook(){[Name="TrainingMatrix"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Doc ID", type text}, {"Category X", type text}, {"Category Y", type text}, {"Category Z", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Doc ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Category"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Doc ID"},PersonnelList,{"Tuck"},"PersonnelList",JoinKind.LeftOuter),
    #"Expanded PersonnelList" = Table.ExpandTableColumn(#"Merged Queries", "PersonnelList", {"Tuck", "Dale", "Ned"}, {"Tuck", "Dale", "Ned"})
in
    #"Expanded PersonnelList"

Here's the PQTable it creates:

        A            B           C       D        E
1     Doc ID      Category     Tuck     Dale     Ned
2      001           Y
3      001           Z
4      002           X          (This stuff will be
5      002           Z          filled in manually.)
6      003           X
7      003           Y

How does your code and the link you first provided work into the solution?

Thanks,
Nick

 

[MarcelBeug]

Well, that looks like "so far so good": you created your table PQTable and now you can replace the PQTable query code by the code I provided in post #4 .
You may want to adjust my step #"Expanded Removed Columns" and replace 2 (at the end) by 4, as you (seem to) have 4 columns preceding the columns with names).
Don't use the last 2 steps of your code (before "in").

Please notice that I deliberately avoided hard coded categories and names (e.g. I removed step #"Changed Type"), so the solution will also work when categories or names are added or deleted.
(In the latter case, you may also loose manually entered data for deleted category/name combinations).

 

[MarcelBeug]

Watch this video in which I delete query and table PQTable, rebuild it from scratch and illustrate how it works, perfectly dynamic.
Remark: I have my refresh button added to the Quick Access Toolbar at the top of the screen, so any time I press this, the queries are refreshed.

https://youtu.be/MHJxRCdnm-s