Help with a dataset

[EhsanAz]

Hi all,

I have a database that is has a format like below and could be many records. I want to import and organize this so I have 7 rows and 1 column for each of the record numbers (that could be different every time) - below there are only 2 records, but it could be any number. The second problem is in that two of the rows (time_stamp row, and gps_lat), when i import, they get imported into multi-columns. I need to have this in one cell with some sort of date/time format for time_stamp and a xx,yyy format for the gps cooridnate one. Is that possible? as shown below each record start with a line <result_rec_start> and ends with a line <result_rec_end>

<result_rec_start>
rec_num,"Record Number",1
rec_ver,"Record version",0
time_stamp,"Time stamp: day/month/year/hour/minute/second",26,6,2019,4,46,18
operator,"Operator name or ID","OPERATOR 1"

gps_Lat,"GPS Latitude: degrees/minutes/seconds",45,6,484.8600
test_type,"Test Type", CURRENT BRANCH
tuned_freq,"Tuned Frequency",69.0

<result_rec_end>

<result_rec_start>
rec_num,"Record Number",2
rec_ver,"Record version",0
time_stamp,"Time stamp: day/month/year/hour/minute/second",26,6,2019,4,55,22
operator,"Operator name or ID","OPERATOR 1"

gps_Lat,"GPS Latitude: degrees/minutes/seconds",45,6,484.8600
test_type,"Test Type", CURRENT BRANCH
tuned_freq,"Tuned Frequency",70.0

<result_rec_end>

[lbendlin]

That's a nicely insane data format.  Here's a proposal to tackle this in Power Query.  You will have to adjust it for the real data (I noticed that Longitude is missing)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3VFNT4QwEP0rDedmKQi4621d14/EsJuKJ9gQhHGXxBbshwn/3qESOZiYPXnw0s6befNm2pfnXmEZu6gVaPtmSgV1qU2ljMuCd6C5N+akFbTwONSdakhqxQuowqPBd/0D1FxHoNtOIoE5gmkFjKqiR06GgDhwRZpq8EUnzckfoFL+qbPKF620BnyNUrJ**bleep**CQ1ZsKIRjRIaLJ1k14OqTDcO3U0hkRUq4/1wg22Y32/5OttxEhSe63HHsdflY2Wwfrd/Ihi1xjaAq8BRAehpvJ7ma1SKYlwgWkaLZcKm94A2pRl6GJ+DMckwRibZPHO+TTNyzdfp5v6LayU05auC95E8AnKLwIKsB2xJVgs2L/fDCsAvmI1wB/mFfr5z4R87F8c0DP+Vc5fsXOcOnw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "" and [Column1] <> " " and [Column1] <> "<result_rec_end>" and [Column1] <> "<result_rec_start>")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Row", each Number.Mod([Index],7),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Item", each Number.IntegerDivide([Index],7),Int64.Type),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Column1", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Column", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Description", "Value"}),
    #"Added Custom2" = Table.AddColumn(#"Split Column by Delimiter1", "Converted", each if [Column]="time_stamp" then 
   let t = Text.Split([Value],",")
   in #datetime(Number.From(t{2}),Number.From(t{1}),Number.From(t{0}),Number.From(t{3}),Number.From(t{4}),Number.From(t{5}))  
else if [Column]="gps_Lat" then 
   let t= Text.Split([Value],",")
   in Number.From(t{0})+Number.From(t{1})/60+Number.From(t{2})/3600
else [Value]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Column", "Item", "Converted"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Column]), "Column", "Converted"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"rec_num", Int64.Type}, {"rec_ver", Int64.Type}, {"time_stamp", type datetime}, {"operator", type text}, {"gps_Lat", type number}, {"test_type", type text}, {"tuned_freq", type number}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".