Search code examples
excelpowerbipowerquerypowerbi-desktopm

Unconventional text file format: Mapping "dangling" text as column names

What would be a good method to get the labeled column names below to be placed as the actual column names for the numeric fields in the (abridged) text file below?

# Stream: MPDE1 Air
# URL: http://172.17.0.1:8081/joule
#
# start: 2014-05-18 20:57:40 +0000
# end: 2029-09-02 21:49:57 +0000
# total time: over 15 years
# total rows: 1193
# decimation factor: 1024
# notes:
# legend:
# Column 1: time (us)
# Column 2: MPDE1 Charge Air Pressure (no units)
# Column 3: MPDE1 Charge Air Temperature (no units)
# Column 4: MPDE1 Start Air Pressure (no units)
# Column 5: MPDE1 Crankcase Pressure (no units)
# Column 6: MPDE1 Control Air MTU Pressure (no units)
# Column 7: MPDE1 Control Air local Pressure (no units)
# Column 8: MPDE1 Intake Air Temperature (no units)
#
# --------- MATLAB INSTRUCTIONS ------------
#
# this file can be loaded directly into MATLAB
#
# >> x = importdata('~/Downloads/filename.csv')
# x =
# data: [1737x15 double] % the data
# textdata: {41x1 cell} % this help text
#
1.587141691471679e+15 87.0 356.0 1087.5 2.200000047683716 174.0 -0.20000000298023224 356.0
1.588982371460937e+15 87.0 356.0 1087.5 2.200000047683716 174.0 0.06386884301900864 356.0
1.589596771472655e+15 0.0 91.60917663574219 291.8644714355469 0.0 67.76226043701172 0.25987645983695984 84.54258728027344
1.589658211465819e+15 2.0390625 113.2216796875 379.10101318359375 0.05152728036046028 93.59890747070312 1.5738410949707031 91.5315170288086
1.589719651469726e+15 0.0 92.08888244628906 424.5111389160156 0.0 91.92170715332031 2.584010124206543 83.6112289428711
1.589781091454101e+15 0.0 89.78622436523438 424.4468078613281 0.0 91.99052429199219 3.7338995933532715 81.59453582763672
1.589842531461913e+15 0.0 89.03964233398438 424.583740234375 0.0 91.93624114990234 5.337348937988281 80.92362213134766
1.589903971429687e+15 0.05742187425494194 101.93095397949219 425.06207275390625 -0.00048828125 91.98717498779297 7.536652088165283 80.07470703125
1.589965411471679e+15 0.0 88.92433166503906 425.7071838378906 0.0 92.02363586425781 8.792312622070312 78.72703552246094
1.590026851453125e+15 0.0 76.87582397460938 425.2920227050781 0.0 91.92735290527344 10.360462188720703 73.7714614868164
1.590088291436523e+15 0.9345703125 72.80612182617188 432.24908447265625 -1.1497070789337158 92.85986328125 12.713504791259766 73.5042724609375
1.590149731455077e+15 0.4248046875 69.1480712890625 428.84393310546875 -1.4775390625 92.64044189453125 14.61204719543457 70.16329956054688
1.590211171464843e+15 0.0 68.83126831054688 424.8350830078125 0.0 91.89635467529297 17.992916107177734 68.66358947753906
1.590272611469726e+15 0.0 93.70545196533203 424.22998046875 0.0 91.79979705810547 20.77794647216797 78.2529067993164
Solution

  • Quick and dirty.

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Column1], "# Column")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
    columnNames = Table.TransformColumns(#"Removed Other Columns", {{"Column1", each Text.AfterDelimiter(_, ": "), type text}}),
    #"Transposed Table" = Table.Transpose(columnNames),
    Custom1 = #"Changed Type",
    #"Filtered Rows1" = Table.SelectRows(Custom1, each [Column2] <> null),
    #"Appended Query" = Table.Combine({#"Transposed Table", #"Filtered Rows1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true])
in
    #"Promoted Headers"