Hi all,
I also asked for help on the MrExcel forum and have gotten a response from one individual, but we haven't been successful.
I used Power Query to build a custom 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 details:
The table I've set up with Power Query is somewhat complex (for me anyway). It's a combination of 2 tables that I transformed quite a bit before merging. The first table is TrainingMatrix. The second is PersonnelList. Here's what it all looks like.
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've been at this for a few days with some help from other forums but no luck so far.
I've been told the answer lies in creating a self-referencing query by manipulating the code in the advanced editor. Here's the code of PQTable:
Thank you in advance.
-Nick
I also asked for help on the MrExcel forum and have gotten a response from one individual, but we haven't been successful.
I used Power Query to build a custom 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 details:
The table I've set up with Power Query is somewhat complex (for me anyway). It's a combination of 2 tables that I transformed quite a bit before merging. The first table is TrainingMatrix. The second is PersonnelList. Here's what it all looks like.
HTML:
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
PQTable (transforms and merges top 2 tables):
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 YIt'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've been at this for a few days with some help from other forums but no luck so far.
I've been told the answer lies in creating a self-referencing query by manipulating the code in the advanced editor. Here's the code of PQTable:
Code:
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
[COLOR=#333333][FONT='inherit'][COLOR=#222222][FONT=Verdana] #"Expanded PersonnelList"[/FONT][/COLOR][/FONT][/COLOR]-Nick
