Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RobGer
Frequent Visitor

Combine CSV Files with multiple tables monthly

 

 

Hello dear Power BI Community,

 

I have a problem with an automated Dashboard I want to create. I download data from a website on a monthly basis in csv format, and I want to create a Dashboard with a timeline per month with minimal workaround. The file I posted will contain the raw data without any modifications.


I can´t find a way, to transform the csv file into a format that I can work with. The CSV file contains a row for the titel of the table, then x number of values sepparated by commas, and finally an empty row to separate one table from another. When working with only one CSV file, I could manage to sepparate each table by creating a check if each row was empty or not, and indexing the whole document adding + 1 for each empty row the index function finds. This won´t work if I append multiple csv files.

 

Attached you will find the csv file, and the power query entries to recreate what I did.

The expected result would be a main query entry with all of the tables, that I can later reference and filter to get only the table I want (while being able to maintain the "Source Query" Column, which I will be using as a date filter in Dax).

 

let
  Source = SharePoint.Files([SharePoint Folder], [ApiVersion = 15]), 
  #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = [SharePoint Folder])), 
  #"Filtered Hidden Files1" = Table.SelectRows(
    #"Filtered Rows", 
    each [Attributes]?[Hidden]? <> true
  ), 
  #"Invoke Custom Function1" = Table.AddColumn(
    #"Filtered Hidden Files1", 
    "Transform File", 
    each #"Transform File"([Content])
  ), 
  #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), 
  #"Removed Other Columns1" = Table.SelectColumns(
    #"Renamed Columns1", 
    {"Source.Name", "Transform File"}
  ), 
  #"Expanded Table Column1" = Table.ExpandTableColumn(
    #"Removed Other Columns1", 
    "Transform File", 
    Table.ColumnNames(#"Transform File"(#"Sample File"))
  ), 
  // At this Point I have a Table with the Source Name (Date and Name of the Document) and one column with the csv data. So I split it. The next steps involve creating an Index based on the empty rows to separate the different tables, which would work, but not if I want to repeat the process monthly.
  #"Split Column by Delimiter1" = Table.SplitColumn(
    #"Expanded Table Column1", 
    "Column1", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {
      "Column1.1", 
      "Column1.2", 
      "Column1.3", 
      "Column1.4", 
      "Column1.5", 
      "Column1.6", 
      "Column1.7", 
      "Column1.8", 
      "Column1.9", 
      "Column1.10", 
      "Column1.11", 
      "Column1.12", 
      "Column1.13", 
      "Column1.14", 
      "Column1.15", 
      "Column1.16"
    }
  ), 
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Split Column by Delimiter1", 
    "Source.Name", 
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), 
    {"Source.Name.1", "Source.Name.2"}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Split Column by Delimiter", 
    "Datum Source", 
    each 
      if [Column1.1] = "" then
        null
      else
        Text.Start([Source.Name.1], 4) & "." & Text.End([Source.Name.1], 2) & ".01"
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Datum Source", type date}}), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type1", 
    null, 
    "", 
    Replacer.ReplaceValue, 
    {
      "Column1.2", 
      "Column1.3", 
      "Column1.4", 
      "Column1.5", 
      "Column1.6", 
      "Column1.7", 
      "Column1.8", 
      "Column1.9", 
      "Column1.10", 
      "Column1.11", 
      "Column1.12", 
      "Column1.13", 
      "Column1.14", 
      "Column1.15", 
      "Column1.16", 
      "Datum Source"
    }
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Replaced Value", {"Source.Name.1", "Source.Name.2"}), 
  #"Added Custom1" = Table.AddColumn(
    #"Removed Columns", 
    "IsBlank", 
    each List.IsEmpty(List.RemoveItems(Record.ToList(_), {""}))
  ), 
  #"List with Index" = List.Generate(
    () => [x = 0, i = 0], 
    each [i] < Table.RowCount(#"Added Custom1"), 
    each [i = [i] + 1, x = (if #"Added Custom1"[IsBlank]{i} = true then [x] + 1 else [x])], 
    each [x]
  ), 
  #"Create Table with Index" = Table.FromColumns(
    Table.ToColumns(#"Added Custom1") & {#"List with Index"}, 
    Table.ColumnNames(#"Added Custom1") & {"Index"}
  ), 
  #"Filtered Rows1" = Table.SelectRows(#"Create Table with Index", each ([IsBlank] = false)), 
  #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1", {"IsBlank"})
// Now the Tables are indexed, the empty rows have been erased, and I could reference this table and filter through the index column to get the table I want.
in#"Removed Columns1"

 

Sample Data: https://we.tl/t-dRZ3lCehWE

 

Thank you in advance!

2 REPLIES 2
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

I forgot to upload the sample Data, I edited it in the main Post!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors