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!
