Forum Discussion

RobGer's avatar
RobGer
Frequent Visitor
2 years ago
Solved

Transform CSV Files on a monthly basis

I have the following issue regarding transforming csv files that are sent to me monthly.

The files are named after the YearMonth and the analyzed Subject.

Example: 202404 – Web traffic.

 

The file has the following format:

1st row: Title of the 1st table.

2nd row: Column names for that table.

3rd row till last row of the table: Content of that table.

“Empty Row”

After the empty Row comes another table, following the same format as above (Title, column names and content, followed by another empty row).

 

The title of each table stays the same each month, but I can´t somehow come with a solution that allows me to combine the monthly csv files.

The expected result would be, a query for each “table” that has a column with the source name of the file, so that I can later filter by date.

Each Query would be called after the title of each table, example:

Query Besucherüberblick

SourceName

nb_uniq_visitors

nb_visits

nb_actions

max_actions

nb_actions_per_visit

avg_time_on_site

bounce_rate

202403

„Data for 202403“

202404

898

1128

3227

46

2.9

00:02:35

50 %

 

Sample Data for one month: https://we.tl/t-0buiEhFDdS

 

Note: I use the following encoding to translate the file into power BI:

= Csv.Document(File.Contents("File Path"),[Delimiter=",", Columns=16, Encoding=65001, QuoteStyle=QuoteStyle.None])

 

Is this even possible? Thanks in advance!

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    This query reads all files in specified folder and grabs all tables. Replace full_folder_path with your own and give it a try.  

    let
    file_processing = (b as binary) as table =>
        [s = Lines.FromBinary(b, QuoteStyle.None, false, 65001),
        rm = Table.FromColumns({List.RemoveItems(s, {"", "message", "ok"})}, {"table_name"}),
        group = Table.Group(
            rm, "table_name", 
            {"data", (x) => Table.PromoteHeaders(
                Table.FromList(List.Skip(x[table_name]))
            )}, 
            GroupKind.Local, 
            (s, c) => Number.From(not Text.Contains(c, ","))
        )][group],
    files = Folder.Files("full_folder_path")[[Name], [Content]],
    tra = Table.TransformColumns(files, {"Content", file_processing}),
    xpand = Table.ExpandTableColumn(tra, "Content", {"table_name", "data"})
in
    xpand

     

     

     

     

     

     

     

     

    Then you may create a reference to this query, filter by table name and expand data column to get what you want.

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    This query reads all files in specified folder and grabs all tables. Replace full_folder_path with your own and give it a try.  

    let
    file_processing = (b as binary) as table =>
        [s = Lines.FromBinary(b, QuoteStyle.None, false, 65001),
        rm = Table.FromColumns({List.RemoveItems(s, {"", "message", "ok"})}, {"table_name"}),
        group = Table.Group(
            rm, "table_name", 
            {"data", (x) => Table.PromoteHeaders(
                Table.FromList(List.Skip(x[table_name]))
            )}, 
            GroupKind.Local, 
            (s, c) => Number.From(not Text.Contains(c, ","))
        )][group],
    files = Folder.Files("full_folder_path")[[Name], [Content]],
    tra = Table.TransformColumns(files, {"Content", file_processing}),
    xpand = Table.ExpandTableColumn(tra, "Content", {"table_name", "data"})
in
    xpand

     

     

     

     

     

     

     

     

    Then you may create a reference to this query, filter by table name and expand data column to get what you want.

    • RobGer's avatar
      RobGer
      Frequent Visitor
      2 years ago

      Thanks a lot! This has been eating at me for the past weeks, the solution worked perfectly!