Data cleaning using excel - CSV Vendor file

[melvin]

Hi Team, I have been tasked to reconcile some invoices where the file comes in an absolutely crazy format.

I would sincerely appreciate assistance where I can see the 4 columns as output to start my reconciliation process.

Regards,

Melv.

 

[Peter Bartholomew]

I believe Power Query is the correct tool for importing and cleaning multiple csv files. It will append and unpivot data as well as sorting dates from strange locales.

 

[melvin]

Thanks Peter. I will try and influence the business on power query. However based on where I currently stand power query will be a next step to learn and start using it

 

[Peter Bartholomew]

Its not that difficult and PQ is part of Excel. The attached copy illustrates the sort of transformations that are possible.
I not that the data is in an obsolete format. If you are using versions of Excel prior to 2010 then PQ is not available to you.

 

[vletm]

melvin
Could You upload a sample CSV-file as a source?
and
expected output as an Excel-file based Your sample CSV-file?
if You would like to avoid to see
... whole CSV-files data with Excel.

 

[melvin]

hey mate, the previous data file attached was a csv version which showcases how the data comes from the vendor. I have attached a sample excel file which I would like to data to come as.

 

[vletm]

melvin
Mate who?
Didn't You have idea to get something from CSV-file to Excel-file?
You have offered Excel-files .... even for input.

 

[AlanSidman]

As Peter suggested, PQ is an option. Here is the Mcode to achieve your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"T/AS", "Column3", "Column2", "Column15"}),
    #"Filled Down" = Table.FillDown(#"Removed Other Columns",{"Column2", "Column15"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"T/AS"] = 5 or [#"T/AS"] = 6)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","CHILDREN","",Replacer.ReplaceText,{"Column15"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","[","",Replacer.ReplaceText,{"Column15"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","]","",Replacer.ReplaceText,{"Column15"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value2",{{"T/AS", "Quantity"}, {"Column3", "Rate"}, {"Column2", "Shop"}, {"Column15", "Code"}})
in
    #"Renamed Columns"