Forum Discussion
Solved
Record Field manipulation
I have a text file that I import into Power BI.
This is an extract of database information about tablespace useage.
At the beginning of each file is header that looks like this:
Line 1: Current Date dd\MM\YY hh:mm:ss
Line 2: Header Title 1 Header Title 2 Header Title 3 Header Title 4 Header Title 5
The file contains data collected on an hourly basis and the header is repeated in the file for each hour looking like this:
Header
Data
Header
Data
The amount of rows in the data is dynamic and what I want to do is take the data/time value from line one in the header, and populate a column with the date and time for each different data section in the file.
Any help?
I do it from Menu Option and obtain this Code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTEktMlTSgbKM4CxjOMsEzjJVitWJVnIE8kE6nIEYpD6CSFFDEtSawEWdS4uKUvNKFFISS1KtFAwM9YHIyMDQXMHQ1MrAAIiAKhEIpIMcPzlBXQKy2xJuN0jUFKuoMVZR7CYYYfgUt5/MqOgnX2g4guy2gNuNLGqEImqOVa0FVrVo5sYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current date: 01/01/2017 14:00:00" = _t, #"(blank)" = _t, #"(blank) (2)" = _t, #"(blank) (3)" = _t, #"(blank) (4)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current date: 01/01/2017 14:00:00", type text}, {"(blank)", type text}, {"(blank) (2)", type text}, {"(blank) (3)", type text}, {"(blank) (4)", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each if Text.Start([Column1],13)="Current date:" then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1] = "Header1" then "Remove" else if Text.StartsWith([Column1], "Current date") then "Remove" else null ), #"Removed Top Rows" = Table.Skip(#"Added Custom1",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Remove] <> "Remove"), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remove"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Current date: 01/01/2017 14:00:00", "Current Date"}}), #"Extracted Text Range" = Table.TransformColumns(#"Renamed Columns", {{"Current Date", each Text.Middle(_, 14, 20), type text}}), #"Cleaned Text" = Table.TransformColumns(#"Extracted Text Range",{{"Current Date", Text.Clean}}), #"Changed Type1" = Table.TransformColumnTypes(#"Cleaned Text",{{"Current Date", type datetime}}) in #"Changed Type1"And this is the Video:
1 Reply
I do it from Menu Option and obtain this Code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTEktMlTSgbKM4CxjOMsEzjJVitWJVnIE8kE6nIEYpD6CSFFDEtSawEWdS4uKUvNKFFISS1KtFAwM9YHIyMDQXMHQ1MrAAIiAKhEIpIMcPzlBXQKy2xJuN0jUFKuoMVZR7CYYYfgUt5/MqOgnX2g4guy2gNuNLGqEImqOVa0FVrVo5sYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current date: 01/01/2017 14:00:00" = _t, #"(blank)" = _t, #"(blank) (2)" = _t, #"(blank) (3)" = _t, #"(blank) (4)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current date: 01/01/2017 14:00:00", type text}, {"(blank)", type text}, {"(blank) (2)", type text}, {"(blank) (3)", type text}, {"(blank) (4)", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each if Text.Start([Column1],13)="Current date:" then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1] = "Header1" then "Remove" else if Text.StartsWith([Column1], "Current date") then "Remove" else null ), #"Removed Top Rows" = Table.Skip(#"Added Custom1",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each [Remove] <> "Remove"), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remove"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Current date: 01/01/2017 14:00:00", "Current Date"}}), #"Extracted Text Range" = Table.TransformColumns(#"Renamed Columns", {{"Current Date", each Text.Middle(_, 14, 20), type text}}), #"Cleaned Text" = Table.TransformColumns(#"Extracted Text Range",{{"Current Date", Text.Clean}}), #"Changed Type1" = Table.TransformColumnTypes(#"Cleaned Text",{{"Current Date", type datetime}}) in #"Changed Type1"And this is the Video: