Data Retrieve From CSV file to excel by Power Query

[Bibhuti Mohanty]

Dear All Excel Expert,
I would like to sincere thanks for all the effort you have made for share your knowledge on excel. I want to combine multiple csv file from a folder to one and attached here with the sample file. The csv file has multiple delimiter and space.
Thanks for your consideration. Waiting for your valuable reply.
Regards,

 

[GraH - Guido]

Hi Bibhuti Mohanty,

The trick in the transformation is something like
- Remove Top Rows (3)
- Remove Last Rows (1)
- Remove row containing "----"
- Add custom column= Text.Combine(List.Select(Text.Split("Column1", " "), each _ <> ""), " ")
- Split column by delimiter "space"
- Promote headers

=> change the source step so it refers to your folder path.

let
    Source = Folder.Files("G:\Chandoo_44941"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    GetContent = Table.AddColumn(#"Removed Other Columns", "GetContent", each Csv.Document([Content],[Delimiter="""", Encoding=1252])),
    #"Removed Other Columns1" = Table.SelectColumns(GetContent,{"GetContent"}),
    SkipTop3Last = Table.AddColumn(#"Removed Other Columns1", "SkipTop3Last", each Table.Range([GetContent],3,Table.RowCount([GetContent])-4)),
    #"Removed Other Columns2" = Table.SelectColumns(SkipTop3Last,{"SkipTop3Last"}),
    #"Expanded SkipTop3Last" = Table.ExpandTableColumn(#"Removed Other Columns2", "SkipTop3Last", {"Column1"}, {"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded SkipTop3Last", each not Text.Contains([Column1], "----")),
    Normalized = Table.AddColumn(#"Filtered Rows", "Normalized", each Text.Combine(List.Select(Text.Split([Column1], " "), each _ <> "")," ")),
    #"Removed Other Columns3" = Table.SelectColumns(Normalized,{"Normalized"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns3", "Normalized", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Normalized.1", "Normalized.2", "Normalized.3", "Normalized.4", "Normalized.5", "Normalized.6", "Normalized.7", "Normalized.8", "Normalized.9", "Normalized.10", "Normalized.11", "Normalized.12", "Normalized.13", "Normalized.14", "Normalized.15", "Normalized.16", "Normalized.17", "Normalized.18", "Normalized.19", "Normalized.20", "Normalized.21", "Normalized.22"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Normalized.1", type text}, {"Normalized.2", type text}, {"Normalized.3", type text}, {"Normalized.4", type text}, {"Normalized.5", type text}, {"Normalized.6", type text}, {"Normalized.7", type text}, {"Normalized.8", type text}, {"Normalized.9", type text}, {"Normalized.10", type text}, {"Normalized.11", type text}, {"Normalized.12", type text}, {"Normalized.13", type text}, {"Normalized.14", type text}, {"Normalized.15", type text}, {"Normalized.16", type text}, {"Normalized.17", type text}, {"Normalized.18", type text}, {"Normalized.19", type text}, {"Normalized.20", type text}, {"Normalized.21", type text}, {"Normalized.22", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([PONO] <> "PONO"))
in
    #"Filtered Rows1"