Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to create a report that each week import a csv file come from ERP.
let Source = Csv.Document(File.Contents("D:\Lavori\01.30_-_Studio Power BI\stock values csv"),[Delimiter="|", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),
This report has to summarize the stock value for each storage location. But next Monday, I will receive a new file.
Then I would like to clean the model to elaborate new data. If I put the new data in the same path and using the same filename, which code should I add after let or source ?
I would be very grateful if you could help me to solve this issue.
Solved! Go to Solution.
Yes, I have tested it, if the data on the file is
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
Then you will not get the row after refersh.
222 b 6 80
Regards,
Charlie Liao
If you put the file in the same place using the same name you should just need to refresh the data.
Proud to be a Super User!
Thank you very much for your reply.
Pheraps I am a bit confusing. Up to now I have made reports where the total number of rows always increases.
In this case each week I get a sort of "picture" take in that moment.
My doubt is the following. Let us suppose that in th week 1 we have
Material Project
sorry ... early click...
Thank you very much for your reply.
Pheraps I am a bit confusing. Up to now I have made reports where the total number of rows always increases.
In this case each week I get a sort of "picture" take in that moment.
My doubt is the following. Let us suppose that in th week 1 we have:
Material Project qty value
111 a 3 20
222 b 6 80
333 c 5 70
Then in the week 2 we have:
Material Project qty value
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
The row 444 wil be added since it is new. Same happen for row 222 e.
Row 222 b should be refreshed too. But what about the original row:
222 b 6 80
will be canceled from the model?
This is the point not clear to me.
As long as the data exists in your new file it should exist in the query.
It is also possible to add multiple csv files and have the query append the new ones. See here for that: http://www.dutchdatadude.com/combining-text-csv-files-using-power-query-for-excel/
Proud to be a Super User!
Really I would not append anything neither in the week 2 nor in the weeks 3,4,5,.....
I fear that the row below
222 b 6 80 (week 1)
remain in the model data although has been split in two rows
222 b 3 40
222 e 3 40
If this happen the stock value for material 222 woud be 12.
222 b 6 80 (week 1)
222 b 3 40 (week 2)
222 e 3 40 (week 2)
And it is wrong since it should be 6
This is why I would eliminate existing rows each time before refreshing data.
I hope I have been clear.
Hi @gpiero,
For week2, what's the data looks like in your new file?
Material Project qty value
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
OR
Material Project qty value
111 a 3 20
222 b 6 80
333 c 5 70
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
If the data in new file looks like the former, then you just need to refreshe the data. If the data looks like the latter, could you please give the expected result, so that we can make further analysis.
Regards,
Charlie Liao
Many thanks for your clarification.
I confirm that the new file will contain data in the following form
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
Then I assume the refresh is the solution because the data model will containg nomore the row below
222 b 6 80
Is this understanding correct?
Regard
Yes, I have tested it, if the data on the file is
111 a 3 20
222 b 3 40
222 e 3 40
333 c 5 70
444 f 10 100
Then you will not get the row after refersh.
222 b 6 80
Regards,
Charlie Liao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |