Help to improve data load from TXT files

Anonymous · ‎06-25-2020

Hello,

My PowerBI Desktop data load is very likely not optimal, as I actually load the same data 2x into two separate tables; [Counts] and [Counts per Area/Date]. In short loading my data takes a long time.

[Counts] is basically the raw data, but reworked a bit for locale settings etc.
[Counts per Area/Date] is the same raw data, but filtered much more, so that if there are more sensors in the same area - only the sensor with the highest value for the given date will be used.

I suspect that I should had only made the [Counts per Area/Date] as a calculation based on [Counts] instead - but how, please?

Background info:

I have multiple sensors, each producing TXT log files, which I have placed in separate folders - as the log file is named in the same way.

Raw data example:

Mon,03/26/2018,3:04:01,PM,1,20°C,9.69
Mon,03/26/2018,3:04:04,PM,2,20°C,9.70
Mon,03/26/2018,3:05:13,PM,3,19°C,9.74
Mon,03/26/2018,3:05:15,PM,4,19°C,9.74
Mon,03/26/2018,3:05:28,PM,5,19°C,9.71
Mon,03/26/2018,3:05:43,PM,6,19°C,9.71

Power Querys to load data today:

[Counts]

let
    Source = Folder.Files("C:\Users\Christian.Botved.Pou.ABSLBS\Dropbox\Privat\SSSL\Tællere"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fx([Folder Path]&[Name])),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Name", "Name - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Name - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name - Copy.1", "Name - Copy.2", "Name - Copy.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name - Copy.1", type text}, {"Name - Copy.2", type text}, {"Name - Copy.3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Name - Copy.2"] = "TXT")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name - Copy.2", "Name - Copy.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Folder Path", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.8", "Folder Path.9", "Folder Path.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Folder Path.1", type text}, {"Folder Path.2", type text}, {"Folder Path.3", type text}, {"Folder Path.4", type text}, {"Folder Path.5", type text}, {"Folder Path.6", type text}, {"Folder Path.7", type text}, {"Folder Path.8", type text}, {"Folder Path.9", type text}, {"Folder Path.10", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([#"Name - Copy.1"] = "TC200LOG") and ([Folder Path.9] = "")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Name", "Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.9", "Folder Path.10", "Name - Copy.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Folder Path.8", "Monitor"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Column2", "Date"}, {"Custom.Column1", "Weekday"}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Custom.Column3", type text}}, "da-DK"),{"Custom.Column3", "Custom.Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Time"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Custom.Column5"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns2", {{"Custom.Column6", each Text.BeforeDelimiter(_, "°"), type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Custom.Column6", "Temperature"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Custom.Column7", type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Custom.Column7", "Battery"}}),
    #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns3", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Removed Columns3" = Table.RemoveColumns(#"Inserted Day Name",{"Weekday"}),
    #"Inserted Year" = Table.AddColumn(#"Removed Columns3", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Year", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Renamed Columns7" = Table.RenameColumns(#"Inserted Hour",{{"Monitor", "Location"}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns7", "Location", "Location - Copy"),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Duplicated Column4", {{"Location - Copy", each Text.BeforeDelimiter(_, " -"), type text}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"Location - Copy", "Area"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns8", each [Date] <> null and [Date] <> ""),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each [Date] > #date(2018, 1, 1)),
    #"Renamed Columns9" = Table.RenameColumns(#"Filtered Rows3",{{"Location", "Name"}}),
    #"Duplicated Column5" = Table.DuplicateColumn(#"Renamed Columns9", "Name", "Name - Copy"),
    #"Renamed Columns10" = Table.RenameColumns(#"Duplicated Column5",{{"Name - Copy", "Location"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns10", {{"Location", each Text.AfterDelimiter(_, "- "), type text}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Extracted Text After Delimiter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Changed Type5" = Table.TransformColumnTypes(#"Inserted Week of Year",{{"Date", type date}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Changed Type5",{{"Temperature", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type6",{{"Temperature", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

[Counts per Area/Date]

let
    Source = Folder.Files("C:\Users\Christian.Botved.Pou.ABSLBS\Dropbox\Privat\SSSL\Tællere"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fx([Folder Path]&[Name])),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Name", "Name - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Name - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name - Copy.1", "Name - Copy.2", "Name - Copy.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name - Copy.1", type text}, {"Name - Copy.2", type text}, {"Name - Copy.3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Name - Copy.2"] = "TXT")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name - Copy.2", "Name - Copy.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Folder Path", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.8", "Folder Path.9", "Folder Path.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Folder Path.1", type text}, {"Folder Path.2", type text}, {"Folder Path.3", type text}, {"Folder Path.4", type text}, {"Folder Path.5", type text}, {"Folder Path.6", type text}, {"Folder Path.7", type text}, {"Folder Path.8", type text}, {"Folder Path.9", type text}, {"Folder Path.10", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([#"Name - Copy.1"] = "TC200LOG") and ([Folder Path.9] = "")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Name", "Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7", "Folder Path.9", "Folder Path.10", "Name - Copy.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Folder Path.8", "Monitor"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Column2", "Date"}, {"Custom.Column1", "Weekday"}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Custom.Column3", type text}}, "da-DK"),{"Custom.Column3", "Custom.Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Time"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Custom.Column5"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns2", {{"Custom.Column6", each Text.BeforeDelimiter(_, "°"), type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Custom.Column6", "Temperature"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Custom.Column7", type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Custom.Column7", "Battery"}}),
    #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns3", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Removed Columns3" = Table.RemoveColumns(#"Inserted Day Name",{"Weekday"}),
    #"Inserted Year" = Table.AddColumn(#"Removed Columns3", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Year", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Renamed Columns7" = Table.RenameColumns(#"Inserted Hour",{{"Monitor", "Location"}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns7", "Location", "Location - Copy"),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Duplicated Column4", {{"Location - Copy", each Text.BeforeDelimiter(_, " -"), type text}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"Location - Copy", "Area"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns8", each [Date] <> null and [Date] <> ""),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each [Date] > #date(2018, 1, 1)),
    #"Renamed Columns9" = Table.RenameColumns(#"Filtered Rows3",{{"Location", "Name"}}),
    #"Duplicated Column5" = Table.DuplicateColumn(#"Renamed Columns9", "Name", "Name - Copy"),
    #"Renamed Columns10" = Table.RenameColumns(#"Duplicated Column5",{{"Name - Copy", "Location"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns10", {{"Location", each Text.AfterDelimiter(_, "- "), type text}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Extracted Text After Delimiter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Changed Type5" = Table.TransformColumnTypes(#"Inserted Week of Year",{{"Date", type date}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Changed Type5",{{"Temperature", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type6", {"Area", "Location", "Date"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Area", "Date"}, {{"Count", each List.Max([Count]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Date", Order.Descending}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Sorted Rows",{{"Count", Int64.Type}}),
    #"Tilføjet indeks" = Table.AddIndexColumn(#"Changed Type4", "Indeks", 1, 1),
    #"Omrokerede kolonner" = Table.ReorderColumns(#"Tilføjet indeks",{"Indeks", "Area", "Date", "Count"}),
    #"Fjernede kolonner" = Table.RemoveColumns(#"Omrokerede kolonner",{"Indeks"})
in
    #"Fjernede kolonner"

Icey · ‎06-25-2020

Hi @Anonymous

What does "fx(...)" mean?

I can't reproduce your operations in Power BI Desktop. Please give me more details.

Best Regards,

Icey