none
Power Query refresh removes manually entered data RRS feed

  • Question

  • Hello all,

    I've 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. I hope it comes out okay in the post. If not I'll attach pictures.

    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      Engr
    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           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'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:

    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"

    Someone turned me onto this YouTube video but it hasn't helped me so far: https://youtu.be/duNYHfvP_8U?list=PL...gGUW5dOsKg74mQ

    This is my first time using Power Query. Thanks in advance.

    -Nick









    • Edited by nryan323 Tuesday, October 3, 2017 12:31 AM fixed table
    Tuesday, October 3, 2017 12:23 AM

Answers

  • This illustrates perfectly why cross posting is not appreciated.

    On the MrExcel Power BI forum, I already provided a tailor made solution (post #4) with complete instructions, including a video (in post #10).

    • Marked as answer by nryan323 Wednesday, October 4, 2017 5:00 PM
    Wednesday, October 4, 2017 12:49 PM

All replies

  • Well, that someone was me.

    Cross posting is typically not well appreciated as helpers may find out later that they provided a solution for an issue that was already solved elsewhere.
    If you feel you need additional help, you should at least provide a link to the original topic and an explanation why you raised the same topic in another forum. 

    For now, my suggestion would be to continue with the topic on the MrExcel Power BI forum.

    Of course, anybody can participate.

    I will take another look later.

    Tuesday, October 3, 2017 3:14 AM
  • Hi Nick,

    I already made a detailed explanation about how to do it in this thread:

    How can I add a new column to the workbook so data can be entered in addition to the results loaded by Power Query?


    • Edited by anthony34 Tuesday, October 3, 2017 6:56 AM
    Tuesday, October 3, 2017 5:23 AM
  • Hi Nick,

    if you are still struggling here, you can download a workbook with a self-referencing query here: http://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/

    The key is to create a query first that loads to the sheet and then to change the source of that query to itself (in order to do this, you have to create it first, which results in changing code actually). So when studying the file you don't see the query as it has been created originally, but with the already changed "Source"-step.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, October 4, 2017 10:54 AM
    Moderator
  • This illustrates perfectly why cross posting is not appreciated.

    On the MrExcel Power BI forum, I already provided a tailor made solution (post #4) with complete instructions, including a video (in post #10).

    • Marked as answer by nryan323 Wednesday, October 4, 2017 5:00 PM
    Wednesday, October 4, 2017 12:49 PM
  • Hi all,

    As Marcel Beug wrote in a reply to this thread he already solved the issue for me. See his reply below.

    Marcel created a video which is very helpful along with his replies. Apologies for blasting this on a few forums.

    -Nick


    • Edited by nryan323 Wednesday, October 4, 2017 4:59 PM
    Wednesday, October 4, 2017 4:57 PM
  • Marcel - i totally get what you are saying. I appreciate (as im sure we all do) your efforts and taking the time to make the solution. I just not sure know how burning the OP really furthers your agenda of stopping cross posting. Think of if from his side... How many millions of posts to sift through and maybe he missed one. ... from your perspective you should be happy its another place to showcase your talent... instead you showcased your bad mood  :-)

    Joey Freyre

    Tuesday, June 12, 2018 11:05 AM
  • Googling on just this question brought me to this message first, not the other links listed below or StackOverflow, Reddit, et al.

    I'm going to politely throw in a "oh, WAAAH!", as in, stop being so pedantic. This particular ship has long ago sailed and been sunk by search engines. Seeing "don't cross-post!" responses is about as helpful to everyone else (except that poster's ego) as seeing "RTFM!".

    Monday, April 1, 2019 6:13 PM