Import and clean up a CSV file in Excel

[LibreeaPEV]

Hi All,

I have been provided with a csv output file (over which I have no control) to import into Excel/Power BI so I can report on it. this file is updated overnight and has hundreds of rows, so it is not practical to update it manually. Its structure is as follows:

[blank] URL Number of Vistors
01/01/2019
1 http:www.something.com 2
2 http:www.somethingelse.com 2
3 http:www.somethingmore.com 2
02/02/2019
1 http:www.something.com 2
2 http:www.somethingelse.com 2
3 http:www.somethingmore.com 2

I need it to be in a format something like

1 http:www.something.com 2 01/01/2020
2 http:www.somethingelse.com 2 01/01/2020
3 http:www.somethingmore.com 2 01/01/2020
1 http:www.something.com 2 01/01/2019
2 http:www.somethingelse.com 2 01/01/2019
3 http:www.somethingmore.com 2 01/01/2019

Any assistance would be gratefully received

 

[sandy666]

try add Conditional Column for date
fill down
filter url column by date (year)

assumed date is in url column

 

[LibreeaPEV]

Thaks for the fast response. The Date and the reference number are in the same column unfortunately posting has changed the formatting. Screen shot attached...

 

[sandy666]

column with dates will be as Column1 in PQ Editor
so
change type to text for Column1
if Column1 contain / then Column1 else null
fill down
for Column1 filter - does not contain /
then set data type for each column as you wish

be aware Mon 13/01/2020 is not a date, this is the text in PQ

 

[sandy666]

this is for post#2 but rule is similar

 

[LibreeaPEV]

Thanks for this - did really well to a point, in PQE I now have a new column with Dates and Null in - but couldn't see at which point where I add fill down and to My Steps are Import in PQE > Remove top tows Added additional column (image below) but no option to fill down to 0 If I use it from the transform tab - I get no change

 

[sandy666]

change type to text for Column1
Custom: if Column1 contain / then Column1 else null
select Custom column
fill down
for Column1 text filter - does not contain /
then set data type for each column as you wish

could you post whole M-code you did? use tags

from post menu/ribbon

 

[LibreeaPEV]

Brilliant thank you so much

 

[sandy666]

you are welcome
Thanks for the feedback