Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
54 | |
27 | |
15 | |
14 | |
13 |