Created A Power Query table. I want to link a table to it allowing users to add customs lines

%3CLINGO-SUB%20id%3D%22lingo-sub-2310909%22%20slang%3D%22en-US%22%3ECreated%20A%20Power%20Query%20table.%20I%20want%20to%20link%20a%20table%20to%20it%20allowing%20users%20to%20add%20customs%20lines%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310909%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%20to%20all%20!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20an%20Excel%20file%20with%202%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20sheet%20called%20%22Portefeuille%22%20is%20a%20PowerQuery%20table%20getting%20the%20info%20from%20an%20excel%20sheet%20on%20Sharepoint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20sheet%20called%20%22Team%20A%22%20with%20a%20table%20that%20should%20take%20the%20info%20from%20the%20Power%20Query%20table%20in%20the%20sheet%20%22Portefeuille%22%20and%20use%20it%20to%20update%20itself.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20aim%20to%20allow%20users%20to%20manualy%20add%20lines%20to%20the%20sheet%20%22Team%20A%22%20by%20duplicating%20an%20existing%20project%2C%20for%20example%20duplicating%20the%20line%20of%20the%20project%20A.%20How%20can%20I%20do%20that%3F%3C%2FLI%3E%3CLI%3EI%20want%20the%20duplicated%20lines%20to%20also%20update%20some%20infos%20from%20the%20PowerQuery%20using%20the%20%22ID%20Number%22%2C%20for%20example%202020-1%20for%20Project%20A.%20The%20infos%20that%20should%20be%20updated%20would%20be%20the%20date%20area%20from%20column%20J%20to%20Z.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThis%20is%20no%20easy%20task.%20Do%20you%20have%20something%20in%20mind%3F%3CBR%20%2F%3EPlease%20see%20the%20Excel%20inclosed.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20help%2C%3C%2FP%3E%3CP%3ERegards%2C%3CBR%20%2F%3ELotam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2310909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338444%22%20slang%3D%22en-US%22%3ERe%3A%20Created%20A%20Power%20Query%20table.%20I%20want%20to%20link%20a%20table%20to%20it%20allowing%20users%20to%20add%20customs%20lines%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041849%22%20target%3D%22_blank%22%3E%40Lotam12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20only%20update%20the%20source%20file%20that%20the%20power%20query%20is%20pointing%20to%20get%20the%20data%20from.%3C%2FP%3E%3CP%3EIf%20you're%20going%20to%20allow%20your%20users%20to%20access%20the%20source%20file%20to%20update%20it%2C%20then%20there%20needs%20to%20be%20an%20auditor%2Freviewer%20before%20the%20update%20can%20be%20done.%26nbsp%3B%20Ideally%20the%20query%20should%20be%20from%20a%20folder%20thereby%20the%20power%20query%20will%20just%20combine%20the%20updates%20dropped%20into%20that%20folder%20without%20modifying%20the%20existing%20sources%20that%20way%20if%20there%20were%20errors%20in%20the%20recent%20update%20all%20you%20have%20to%20do%20is%20pull%20that%20particular%20errant%20csv%20file%20so%20it%20can%20be%20corrected%20and%20dropped%20back%20into%20the%20folder%20when%20the%20error%20has%20been%20resolved%2C%20so%20your%20users%20can%20copy%20the%20lines%20from%20the%20query%2C%20put%20it%20on%20their%20excel%20update%20then%20save%20it%20as%20a%20csv%20file%20and%20drop%20their%20update%20into%20the%20folder.%26nbsp%3B%20you%20must%20also%20institute%20a%20file%20naming%20convention%20to%20identify%20who%20created%20the%20update%20and%20when%20the%20file%20was%20created.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good afternoon to all !

I have an Excel file with 2 sheets. 

 

One sheet called "Portefeuille" is a PowerQuery table getting the info from an excel sheet on Sharepoint.

 

One sheet called "Team A" with a table that should take the info from the Power Query table in the sheet "Portefeuille" and use it to update itself. 

 

  • I aim to allow users to manualy add lines to the sheet "Team A" by duplicating an existing project, for example duplicating the line of the project A. How can I do that?
  • I want the duplicated lines to also update some infos from the PowerQuery using the "ID Number", for example 2020-1 for Project A. The infos that should be updated would be the date area from column J to Z.

This is no easy task. Do you have something in mind?
Please see the Excel inclosed.

Thank you very much for your help,

Regards,
Lotam

 

 

2 Replies

@Lotam12 

 

You can only update the source file that the power query is pointing to get the data from.

If you're going to allow your users to access the source file to update it, then there needs to be an auditor/reviewer before the update can be done.  Ideally the query should be from a folder thereby the power query will just combine the updates dropped into that folder without modifying the existing sources that way if there were errors in the recent update all you have to do is pull that particular errant csv file so it can be corrected and dropped back into the folder when the error has been resolved, so your users can copy the lines from the query, put it on their excel update then save it as a csv file and drop their update into the folder.  you must also institute a file naming convention to identify who created the update and when the file was created.

Thank you for your answer.

It is not as flexible as I tought as it requires a certain amount of manoeuvres. Such as it is, I think I will ask users to manualy add the project numbers and automatize most of the other columns with OutlookX formulas.
Still will need a superviser to control the rest with a PowerBI.

Thank you again,
Regards,
Marc