none
Excel can i add Columns Manually to query and preserve row integrity -linkage- when the original table rows number changes? RRS feed

  • Question

  • Good Day I have a table -20 columns- named (employees) with all Basic data of employees in sheet1, and in sheet2 I query 2 columns from the table (employees) and adding 3 columns with manually entered data and formulas. when rows in the table (employees) rearranged or rows deleted the manually columns doesn't affect, can they be linked to follow changes in the main table?

    thanks for the help and sorry for any inconvenience
    Monday, November 4, 2019 2:55 PM

Answers

  • Hello

    here we are.

    Only to specify it in more detail.

    The first solution I proposed can be used if the queried data has to be distributed to other data. Example: You have a project overview and you want to use this data in a parallel process, let's say time schedule overview, sou you can create a master table for time schedules and fetch data of the project overview if you need data from there. But as you stated there would no automatism if rows where added in the project overview. You could create a query that checks both contents and display projects that are not yet present in the time schedule overview. But as this is a parallel process I think this solution fits very well. At least I've implemented a lot.

    Let's make another example were the first solution can not be applied, but you need something more integrated... End user get a list of their deadlines out of the SQL MS PRoject server. This would be a passive information. They update the query and they are informed - stop. But what if they want to add comments, put in the effective data etc., work with it? Create a new table and connect it? Not really -  In such a scenario the first solution would not be appropriate, a connection difficult to establish, error prone. For such a scenario I developed a solution, but never applied it yet :) so you could test it and tell me if it works. And now that I'm investigating my old file... holy shit - nice trick :D

    First of all, the property of your query output table have to be set that cell formatting and layout have to be kept and the second option has to selected in the third paragraph. In german "Ganze Zeilen für neue Daten einfügen, nicht verwendete Zellen löschen". This is really important for the solution.

    Technical describing the solution: you have to create a "loop" meaning for your final table you have to read the original table and incorporate the final table. Very tricky. But it works. However the lines have to have a unique key (also combining multiple columns) where the ouput of query 1 and incorporation of it can be connected. So only use a material number that is not unique wouldn't work...  How could it work?? How to assign your input?

    Solution

    First Query called Origin, and outputs data to the table "OriginAndManualText"  - incorporates his own output

    let

    Quelle = Excel.CurrentWorkbook(){[Name="Origin"]}[Content], ChangedType = Table.TransformColumnTypes(Quelle,{{"a", type text}, {"b", type text}}), ConnectWithOutputOfThisQueryLocatedInAnotherQuerY = Table.NestedJoin(ChangedType,{"a", "b"},OriginAndManualText,{"a", "b"},"StoreOriginAndManualText",JoinKind.FullOuter), Expand = Table.ExpandTableColumn(ConnectWithOutputOfThisQueryLocatedInAnotherQuerY, "StoreOriginAndManualText", {"Index"}, {"Index"}), ReplaceValue = Table.ReplaceValue(Expand,null,999999,Replacer.ReplaceValue,{"Index"}), Sort = Table.Sort(ReplaceValue,{{"Index", Order.Ascending}}), RemoveIndex = Table.RemoveColumns(Sort,{"Index"}) in RemoveIndex

    second query called "OriginAndManualText" that is used to fetch the output the first query and manual data


    let
        Quelle = Excel.CurrentWorkbook(){[Name="OriginAndManualText"]}[Content],
        ChangedType = Table.TransformColumnTypes(Quelle,{{"a", type text}, {"b", type text}, {"Manual", type text}}),
        AddIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1)
    in
        AddIndex

    So, wondering if this could be a solution for you and if you are able to make it work :)

    Good luck

    Jimmy




    Query it

    Tuesday, November 5, 2019 7:33 AM

All replies

  • Hello

    I had the same issue some time ago.

    You have two possibilities to solve this.

    First

    Don't add this manual information to the query output but hide the output, create a new  table, create a link to the hidden output using a key (employee name) and index/match and fetch all data you need.

    Second

    This requires loading the query to a sheet adding a index and loading this again to power query. I don't have the detailed solution by hand. But if you are interested, i can share it here

    Br

    Jimmy


    Query it

    Monday, November 4, 2019 4:00 PM
  • Hello jimmy

    Thank you for response, Appreciated

    In the first solution you've offered would  the new table be expandable if the original one - or the hidden output - did ( new rows added )?
    About the second , yes please share it.

    Thanks a lot

    Monday, November 4, 2019 9:26 PM
  • Hello

    here we are.

    Only to specify it in more detail.

    The first solution I proposed can be used if the queried data has to be distributed to other data. Example: You have a project overview and you want to use this data in a parallel process, let's say time schedule overview, sou you can create a master table for time schedules and fetch data of the project overview if you need data from there. But as you stated there would no automatism if rows where added in the project overview. You could create a query that checks both contents and display projects that are not yet present in the time schedule overview. But as this is a parallel process I think this solution fits very well. At least I've implemented a lot.

    Let's make another example were the first solution can not be applied, but you need something more integrated... End user get a list of their deadlines out of the SQL MS PRoject server. This would be a passive information. They update the query and they are informed - stop. But what if they want to add comments, put in the effective data etc., work with it? Create a new table and connect it? Not really -  In such a scenario the first solution would not be appropriate, a connection difficult to establish, error prone. For such a scenario I developed a solution, but never applied it yet :) so you could test it and tell me if it works. And now that I'm investigating my old file... holy shit - nice trick :D

    First of all, the property of your query output table have to be set that cell formatting and layout have to be kept and the second option has to selected in the third paragraph. In german "Ganze Zeilen für neue Daten einfügen, nicht verwendete Zellen löschen". This is really important for the solution.

    Technical describing the solution: you have to create a "loop" meaning for your final table you have to read the original table and incorporate the final table. Very tricky. But it works. However the lines have to have a unique key (also combining multiple columns) where the ouput of query 1 and incorporation of it can be connected. So only use a material number that is not unique wouldn't work...  How could it work?? How to assign your input?

    Solution

    First Query called Origin, and outputs data to the table "OriginAndManualText"  - incorporates his own output

    let

    Quelle = Excel.CurrentWorkbook(){[Name="Origin"]}[Content], ChangedType = Table.TransformColumnTypes(Quelle,{{"a", type text}, {"b", type text}}), ConnectWithOutputOfThisQueryLocatedInAnotherQuerY = Table.NestedJoin(ChangedType,{"a", "b"},OriginAndManualText,{"a", "b"},"StoreOriginAndManualText",JoinKind.FullOuter), Expand = Table.ExpandTableColumn(ConnectWithOutputOfThisQueryLocatedInAnotherQuerY, "StoreOriginAndManualText", {"Index"}, {"Index"}), ReplaceValue = Table.ReplaceValue(Expand,null,999999,Replacer.ReplaceValue,{"Index"}), Sort = Table.Sort(ReplaceValue,{{"Index", Order.Ascending}}), RemoveIndex = Table.RemoveColumns(Sort,{"Index"}) in RemoveIndex

    second query called "OriginAndManualText" that is used to fetch the output the first query and manual data


    let
        Quelle = Excel.CurrentWorkbook(){[Name="OriginAndManualText"]}[Content],
        ChangedType = Table.TransformColumnTypes(Quelle,{{"a", type text}, {"b", type text}, {"Manual", type text}}),
        AddIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1)
    in
        AddIndex

    So, wondering if this could be a solution for you and if you are able to make it work :)

    Good luck

    Jimmy




    Query it

    Tuesday, November 5, 2019 7:33 AM