Change Header on CSV File - Custom in Query Editor - Weather Data from WSI

pwares,

Try this mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknMzKlU8E8qTi0qS01RcHZx0fdwcVHSUUKgWJ1oEO2Yk5OaV5JfnqcQ4Kigq+Dt6OQKUeCYk5SYV6ngFwkW9XGC6XFxDAGpcAYbBzEUwQYpMNT1cvTTNTIACoGwsbGOKZRpZAFWYISmAMYwsgSqBCkwRlVgZAhjGCnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Header"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" DATE", type date}, {"Allentown PA - KABE CDD", Int64.Type}, {"Allentown PA - KABE HDD", type number}, {"Albany NY - KALB CDD", Int64.Type}, {"Albany NY - KALB HDD", type number}})
in
    #"Changed Type1"