Building the Inventory File

Hi A,

Power Query can do this.

1 - Go to Data Manu
2 - click from Web
3 - add the url, click OK
4 - click Transform Data

Now the data is in PQ and you work in PQ interface




5 - delete the last Changed Type (click the x on its left)
6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)
7 - Right click and rename FILE-csv (1) to Work File-csv



8 - You can edit the headings, move columns from here to there) to have them standardized.
9 - Select Close and Load to



10 - make your choices



If you use a new worksheet, the tab will have the name of the query




11 - Next day open the file
12 - Save as - New name (if you need to preserve the previous data)

13 - right click any cell in the table, select Refresh
It will connect automatically and download the new data from the supplier's csv file
- the new data will replace the old

There are two books on PQ, good ones by Gil Raviv and Ken Pulse.

Success!

G

gelu said:

Hi A,

Power Query can do this.

1 - Go to Data Manu
2 - click from Web
3 - add the url, click OK
4 - click Transform Data

Now the data is in PQ and you work in PQ interface

View attachment 84996


5 - delete the last Changed Type (click the x on its left)
6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)
7 - Right click and rename FILE-csv (1) to Work File-csv

View attachment 84997

8 - You can edit the headings, move columns from here to there) to have them standardized.
9 - Select Close and Load to

View attachment 84998

10 - make your choices

View attachment 85000

If you use a new worksheet, the tab will have the name of the query

View attachment 85001


11 - Next day open the file
12 - Save as - New name (if you need to preserve the previous data)

13 - right click any cell in the table, select Refresh
It will connect automatically and download the new data from the supplier's csv file
- the new data will replace the old

There are two books on PQ, good ones by Gil Raviv and Ken Pulse.

Success!

G

Click to expand...

Thanks buddy. Will try and let you know.

HI Gelu,

Thank you so much for your help. It works and I am almost there to make this automated. However one issue arise that one of the vendor gives the data in a way that it does not repeat the value from one row above. See below screens shot. What I need is to repeat the value from above row.

also can I delete the file at the step 6 created?

6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)

Please advise.

Hello Gelu,

Recently I am having below error while amending one of the supplier for this PQ. Can you please advise why I am not getting data populated.

Hi A,

In the file/Query that gives you trouble:

1 - Open on Source on the right (in Applied Steps)
2 - Check the URL from where the data should be coming from. Is it still valid or do you have to replace it with a new one?

Gelu

gelu said:

Hi A,

In the file/Query that gives you trouble:

1 - Open on Source on the right (in Applied Steps)
2 - Check the URL from where the data should be coming from. Is it still valid or do you have to replace it with a new one?

Gelu

Click to expand...

I have just copy the link provided by the supplier. When I copy the link in the web browser it does download the .csv file.

but some how it doesn't work in PQ.

Any thoughts?

-Ankit

ankrups said:

I have just copy the link provided by the supplier. When I copy the link in the web browser it does download the .csv file.

but some how it doesn't work in PQ.

Any thoughts?

-Ankit

Click to expand...

Can you download it outside of PQ?

gelu said:

Can you download it outside of PQ?

Click to expand...

yes I can download the CSV file outside the PQ.