Power query - two-way data moving

[Pulleritz]

Hello,

Is there any possibility to transfer data with power query two-way?

1. I have one excel file for "orders".

2. From "orders" excel i transfer order rows with category "P" using power query to "purchase" excel and purchasing departments adds purchase info.

3. From "orders" excel i transfer order rows with category "M" using power query to "manufacturing" excel and manufacturing department adds manufacturing info.

4. From "purchase" and "manufacturing" orders i transfer and merge data using power query to "logistic" excel where logistic department adds logistic info.

5. Finally, i want to transfer logistic data from "logistic" excel back to "purchase" and "manufacturing" excel. I tried to use index and match formula but it makes it slow. Can this be done with power query?

Thanks,
Siim

[sandy666]

Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Pulleritz]

Excels and accurate explanation is not so important, main thing is to get to understand is it possible to transfer information back with power query.

But still, adding excel and explain it again.

1. Tellimused.xls in the main excel where all orders are registred.

2. ostmine.xls is the excel for purchase department. Data with category OT and OO is transfered from tellimused.xls using power query and purchase department adds purchase info.

3. tootmine.xls is the excel for manufacturing department. Data with catecory TT is transfered from tellimused.xls using power query and manufacturing department adds manufacturing info.

4. logistika.xls is the excel for logistic department. All data from ostmine.xls and tootmine.xls is merged and transfered using power query and logistic department firstly adds info to column T (staatus(logistika)) /status - ready for delivery, delivered

5. This column value has to be transfered to ostmine.xls and tootmine.xls and also all info from logistika.xls is transfered to projektijuhtimine.xls using power query and there pwoject managers adds planned date of delivery.

6. Planned date of delivery has to be transfered back to logistika.xls.

Right now status from logistika.xls to ostmine.xls and to tootmine.xls and planned date of delivery from projektijuhtimine.xls to logistika.xls is transfered with index,match formula which makes slow. Need to make them faster.

Thanks,
Siim

[sandy666]

1. your data is not well organized
2. your source data is not well defined
3. point 5 about managers add planned date of delivery cannot be done due to lack of information on what and where
4. point 6 cannot be done - see point 3 here
5. I didn't make any corrections, only what was in points 1-4.5

unzip and move folder to the root of C drive so path should be like: C:\pulleritz\(all files here)

if you want to know why data (?) is loading too long - see point 2 here

new columns/tables are green or orange

answer for basic question: is it possible to transfer information back with power query is: yes, it's possible

[Pulleritz]

Whatīs yours opinion how the data should be organised?

Rows sequence get mixed when i sort by category to ostmine and tootmine and after that merge them together. I need to get them to different files, what are the alternatives?

Siim

[sandy666]

Try this book: Database Processing. Fundamentals, Design, and Implementation (David M. Kroenke David J. Auer)
or
this link for the begining: DB intro

Answer for your questions is not one-click advice and exceeds the basic help here, but you can try PQ and DataModel & Relationships

btw. don't use any formatting before your project will be not finished (Excel Tables are not formatting )


If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

[Pulleritz]

Hello again,

Managed to put the tables in connection. Using seperate power query connections.

What are the dangers of this kind system? For me itīs danger to write over rows in tellimused.xls, is there any more?

Siim

[AliGW]

What exactly are you asking us to check? As Sandy has already intimated, this is a very broad brush - you can't expect one of us to trawl through your files looking for issues, as it's beyond the scope of this forum. Be specific, please. Thanks for your co-operation.

[sandy666]

• 1st. use paths what I show you before (C:\test) will be easier for both of us
• 2nd. you need create something like relationship between appropriate tables - (for project managers, manufacturing dept. & logistic dept.) These tables cannot be not connected to appropriate query tables because, like you notice before, cells (data) are moved down or up so tables are incoherent between each other.
• 3rd. you mean overwrite rows with existing data?

[Pulleritz]

I have solverd the incoherence so:

1. Ostmine.xlsx takes rows with category OO and OT from tellimused.xlsx. The order of rows stays same as in tellimused.xlsx

2. Tootmine.xlsx takes rows with categories OT from ostmine.xlsx and they are listed first and also takes rows with categories TT from tellimused.xlsx and they are listed after that (in the same order as in tellimused.xlsx)

3. Logistika.xlsx takes rows with categories OO and OT from ostmine.xlsx and they are listed first (same order as in ostmine and as in tellimused). Also logistika takes rows with categories TT from tootmine and they are listed after that (in the same order as TT rows in tellimused.xlsx).

4. Projektijuhtimine.xlsx takes all info from logistika.xlsx and adds one column for delivery period (all rows stays in the same order as in logistika).

5. Logistika takes back the delivery period from projektijuhtimine (all rows stays in the same order).

My question is although there is no direct connection, is there possible to break system (without inserting new rows between already insterted rows/deleting already inserted rows/and replacing already inserted rows in tellimused.xlsx)?

[sandy666]

If you move any of these files out of path - it will break the system partially or completely, the same will be if you change case size in source headers (PQ is case sensitive)

you can see, eg. these tables are incoherent using your method

sourceproject.jpg resultlogistika.jpg

it shouldn't happen!

and yours DV list in C column on Tellimused.xlsx is set improperly (different ranges)

and on the end example Tellimused.xlsx file.
this is Excel Table. If you want to add next row use Tab key, it will add new row and Jrk nr automatically