Forum Discussion
one column dataset repeated records
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>
3 Replies
let
Source = "<result_rec_start>#(cr)#(lf)rec_num,""Record Number"",1#(cr)#(lf)rec_ver,""Record version"",0#(cr)#(lf)time_stamp,""Time stamp: day/month/year/hour/minute/second"",26,6,2019,4,46,18#(cr)#(lf)operator,""Operator name or ID"",""OPERATOR 1""#(cr)#(lf)#(cr)#(lf)gps_Lat,""GPS Latitude: degrees/minutes/seconds"",45,6,484.8600#(cr)#(lf)test_type,""Test Type"", CURRENT BRANCH#(cr)#(lf)tuned_freq,""Tuned Frequency"",69.0#(cr)#(lf)#(cr)#(lf)<result_rec_end>#(cr)#(lf)#(cr)#(lf) #(cr)#(lf)#(cr)#(lf)<result_rec_start>#(cr)#(lf)rec_num,""Record Number"",2#(cr)#(lf)rec_ver,""Record version"",0#(cr)#(lf)time_stamp,""Time stamp: day/month/year/hour/minute/second"",26,6,2019,4,55,22#(cr)#(lf)operator,""Operator name or ID"",""OPERATOR 1""#(cr)#(lf)#(cr)#(lf)gps_Lat,""GPS Latitude: degrees/minutes/seconds"",45,6,484.8600#(cr)#(lf)test_type,""Test Type"", CURRENT BRANCH#(cr)#(lf)tuned_freq,""Tuned Frequency"",70.0#(cr)#(lf)#(cr)#(lf)<result_rec_end>",
Custom1 = Table.Combine(Table.Group(Table.SelectRows(Table.SplitColumn(Table.FromColumns({Text.Split(Source,"#(lf)")}),"Column1",each List.Skip(Splitter.SplitTextByEachDelimiter({",",","})(_)),{"a","b"}),each [a]<>null),"a",{"n",each Table.PromoteHeaders(Table.Transpose(_))},0,(x,y)=>Byte.From(y="Record Number"))[n])
in
Custom1Thanks wdx223_Daniel - i am a bit new to this, I just loaded the txt file and added a custom step with the fomula you had in Custom1 but gives me an error:
Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=[Table]
Type=[Type]Custom1 = Table.Combine(Table.Group(Table.SelectRows(Table.SplitColumn(Table.FromColumns({Text.Split([YourColumnName],"#(lf)")}),"Column1",each List.Skip(Splitter.SplitTextByEachDelimiter({",",","})(_)),{"a","b"}),each [a]<>null),"a",{"n",each Table.PromoteHeaders(Table.Transpose(_))},0,(x,y)=>Byte.From(y="Record Number"))[n])
