Change Column Headers To Names Dynamically Nested in Column_1 by Column Count

After the source is loaded into Power Bi - Power Query I tab delimit the column which will create 22 columns. The fields in column_1, under "~Curve Information", all the way to the blank field (Rows 23-44), need to become the new column headers for the information found in the rows under "ASCII Data Area".

The problem I face is that the count of columns and the count of associated names is dynamic. The good thing is that the two quantities will always be the same. If I pull the source this month it could be 22 columns but next month it could only be 18 records that were recorded.

Please provide any suggestions.

Need Help

tonmcg

7 years ago

Ok, so this works:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\MyComputer\Desktop\Example Log.las"), null, null, 1252)}),
    #"Added Custom" = Table.AddColumn(Source, "curveFinder", each if Text.Contains([Column1], "~CURVE INFORMATION") then "COLUMNS" else if Text.Contains([Column1], "~ASCII DATA AREA") then "DATA" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"curveFinder"}),
    columnNames =
        let
            #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([curveFinder] = "COLUMNS")),
            #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
            #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
            #"Removed Other Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"curveFinder"}),
            #"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"Column1", each Text.AfterDelimiter(_, " . "), type text}}),
            #"Trimmed Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Column1", Text.Trim, type text}}),
            #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
            #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text"," ","",Replacer.ReplaceText,{"Column1"}),
            Custom1 = #"Replaced Value"[Column1]
        in
            Custom1,
    data =
        let
            #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([curveFinder] = "DATA")),
            #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
            #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"curveFinder"}),
            #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), columnNames)
        in
            #"Split Column by Delimiter"
in
    data

I've tested this on your log file, which contains 22 columns. I also incrementally deleted column names and corresponding data columns as part of my tests. It passed those tests.

While this may work now, it is very brittle and makes a TON of assumptions about the structure of your data in the future. Most important assumptions:

Column names will always fall under '~CURVE INFORMATION' and the data will always fall under '~ASCII DATA AREA'
There will always be a space between the last column name and '~ASCII DATA AREA'
Column names will always be after this delimiter: ' . '
The data values will always be delimited by a space (' ')

I hope this helps.

4 Replies

v-juanli-msft
Community Support
7 years ago
Hi Anonymous

Is the whole data in the Example Log.las your source data which is imported to Power BI?

If so, could you paste some example data here so i can use them to test?

Also, could you show what it look like after importing data into Power BI?

Best Regards
Maggie
- Anonymous
  Not applicable
  7 years ago
  v-juanli-msft
  let Source = Csv.Document(File.Contents("C:\Users\MyComputer\Desktop\Example Log.las"),[Delimiter=" ", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"~VERSION INFORMATION ", type text}, {"", type date}, {"_1", type time}, {"_2", type number}, {"_3", type text}, {"_4", Int64.Type}, {"_5", Int64.Type}, {"_6", Int64.Type}, {"_7", type number}, {"_8", Int64.Type}, {"_9", type number}, {"_10", Int64.Type}, {"_11", Int64.Type}, {"_12", Int64.Type}, {"_13", type number}, {"_14", type number}, {"_15", type number}, {"_16", type number}, {"_17", type number}, {"_18", type number}, {"_19", Int64.Type}, {"_20", Int64.Type}}) in #"Changed Type"
  ~VERSION INFORMATION
  VERS. 3.0 : CWLS LOG ASCII STANDARD -VERSION 3.0
  WRAP. NO   : ONE LINE PER DEPTH STEP
  DLM . TAB : DELIMITING CHARACTER BETWEEN DATA COLUMNS
  ~WELL INFORMATION BLOCK
  STRT .SEC     2012-07-01 05:15:00              : START TIME
  STOP .SEC     2012-07-02 05:15:00              : STOP TIME
  STEP .SEC     1                                : STEP
  NULL .     -999.25                             : NULL VALUE
  COMP.   EXAMPLE                                       :COMPANY
  WELL.   EXAMPLE 21-1HB                                :WELL
     FLD.   MOOREEXAMPLE NE                               :FIELD
     LOC.   270                                           :LOCATION
  PROV.   TX                                            :PROVINCE/STATE
  SRVC.   EXAMPLE SERVICES INC.                         :SERVICE COMPANY
  DATE.   07-02-2012 05:17:30                           :START DATE
  LATI.   32.8419                                       :LATITUDE
  LONG.   -97.5262                                      :LONGITUDE
  GDAT.                                                 :GEODETIC DATA
  CTRY.                                                 :COUNTRY
  ~CURVE INFORMATION
  Hole.ft             : 1 . Hole Depth
  DATE.               : 2 . DATE {MM-DD-YYYY}
  TIME.               : 3 . TIME {hh:mm:ss}
  Gamm.API            : 4 . Gamma Ray
  Bit .Bottom         : 5 . Bit Status {S}
  Pump.psi            : 6 . Pump Pressure
  Diff.psi            : 7 . Diff Press
  Flow.galUS/min      : 8 . Flow In Rate
  Bit .ft             : 9 . Bit Position
  Bit .klb            : 10 . Bit Weight
  Hook.klb            : 11 . Hook Load
  ROP .ft/hr          : 12 . ROP - Average
  Top .RPM            : 13 . Top Drive RPM
  Top .ft·lbf         : 14 . Top Drive Torque
  Gamm.ft             : 15 . Gamma Depth
  Svy .deg            : 16 . Svy Azimuth
  Svy .deg            : 17 . Svy Inclination
  Tool.deg            : 18 . Toolface Mag
  Tool.deg            : 19 . Toolface Grav
  Bloc.ft             : 20 . Block Height
  Pump.SPM            : 21 . Pump SPM 1
  Pump.SPM            : 22 . Pump SPM 2
  ~ASCII DATA AREA
  15613.88 07-01-2012 05:15:00 -999.25 Off 0 -3021 0 2692.96 0.0 44.5 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 24.88 0 0
  15613.88 07-01-2012 05:15:01 -999.25 Off 0 -3021 0 2692.38 0.0 65.8 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 25.46 0 0
  15613.88 07-01-2012 05:15:02 -999.25 Off 0 -3021 0 2691.16 0.0 71.1 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 26.68 0 0
  15613.88 07-01-2012 05:15:03 -999.25 Off 0 -3021 0 2689.35 0.0 72.4 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 28.77 0 0
  15613.88 07-01-2012 05:15:04 -999.25 Off 0 -3021 0 2686.83 0.0 72.4 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 31.01 0 0
  15613.88 07-01-2012 05:15:05 -999.25 Off 0 -3021 0 2683.72 0.0 73.7 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 34.48 0 0
  15613.88 07-01-2012 05:15:06 -999.25 Off 0 -3021 0 2679.90 0.0 73.7 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 37.94 0 0
  15613.88 07-01-2012 05:15:07 -999.25 Off 0 -3021 0 2675.79 0.0 75.4 0.0 0 0 -999.25 332.30 89.40 218.2 -999.25 42.05 0 0
  - Anonymous
    Not applicable
    7 years ago
    v-juanli-msft

tonmcg

Resolver II

7 years ago

Ok, so this works:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\MyComputer\Desktop\Example Log.las"), null, null, 1252)}),
    #"Added Custom" = Table.AddColumn(Source, "curveFinder", each if Text.Contains([Column1], "~CURVE INFORMATION") then "COLUMNS" else if Text.Contains([Column1], "~ASCII DATA AREA") then "DATA" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"curveFinder"}),
    columnNames =
        let
            #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([curveFinder] = "COLUMNS")),
            #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
            #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
            #"Removed Other Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"curveFinder"}),
            #"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"Column1", each Text.AfterDelimiter(_, " . "), type text}}),
            #"Trimmed Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Column1", Text.Trim, type text}}),
            #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
            #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text"," ","",Replacer.ReplaceText,{"Column1"}),
            Custom1 = #"Replaced Value"[Column1]
        in
            Custom1,
    data =
        let
            #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([curveFinder] = "DATA")),
            #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1),
            #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"curveFinder"}),
            #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), columnNames)
        in
            #"Split Column by Delimiter"
in
    data

I've tested this on your log file, which contains 22 columns. I also incrementally deleted column names and corresponding data columns as part of my tests. It passed those tests.

While this may work now, it is very brittle and makes a TON of assumptions about the structure of your data in the future. Most important assumptions:

Column names will always fall under '~CURVE INFORMATION' and the data will always fall under '~ASCII DATA AREA'
There will always be a space between the last column name and '~ASCII DATA AREA'
Column names will always be after this delimiter: ' . '
The data values will always be delimited by a space (' ')

I hope this helps.

Forum Discussion

Change Column Headers To Names Dynamically Nested in Column_1 by Column Count

4 Replies

Related content

Dynamic Column headers

Dynamic Column Header in Visualisation

Dynamic Headers in DAX

Dynamic header

Dynamic Column Header In a Matrix