How to import csv file starting after certain mark

[leisay]

The csv file I want to import has many lines of headers. Then there will be a line with the content: *data. Afterwards it is the real data I need to import to powerbi. Is there any methode that I tell powerBI to look for this mark "*data" then start to import the content afterwards? 

Thanks a lot for your help! 

[amitchandak]

@leisay , if it fixed number ? refer

https://www.excelguru.ca/blog/2019/12/09/remove-dynamic-number-of-top-rows/

https://community.powerbi.com/t5/Power-Query/How-to-dynamically-delete-Top-Rows-Before-a-specific-va...

[leisay]

@amitchandak Thank you for the real quick response. It is not a fixed number. The file looks like the following: 

"HYDAC SMU1270 V03.00 Data File

Start 09.03.2020 04:21:17
RecordCount 14283
Interval
DeviceCount 1

Device 0
Name SMU1270 V03.00
SerNumber 15151
MeasPoint MPNT00
Port 1
Address a
Protocol 0
ChannelCount 10

Channel 0 1 2 3 4 5 6 7 8 9
LowerRange -32768 0 0 0 0 0 0 -60 0 -25
UpperRange 32767 2147483647 2147483647 2147483647 2147483647 2147483647 2147483647 150 100 100
Unit 200-350 350-500 >500 550-750 750-1K >1K ?C % ?C

Comment

*Data*
Date Time State FE A FE B FE C NFE D NFE E NFE F Temp SatLevel Temp
09.03.2020 4:21:17 0 0 0 0 0 0 0 28 27.3 24.68
09.03.2020 4:22:17 0 0 0 0 0 0 0 28 26.28 24.66
09.03.2020 4:23:17 0 0 0 0 0 0 0 28 26.34 24.64
09.03.2020 4:24:17 0 0 0 0 0 0 0 28 27.52 24.58
09.03.2020 4:25:17 0 0 0 0 0 0 0 27.9 26.42 24.63
09.03.2020 4:26:17 0 0 0 0 0 0 0 27.9 26.65 24.61
09.03.2020 4:27:17 0 0 0 0 0 0 0 27.9 26.53 24.58
09.03.2020 4:28:17 0 0 0 0 0 0 0 27.9 26.83 24.59
09.03.2020 4:29:17 0 0 0 0 0 0 0 27.9 26.27 24.58
09.03.2020 4:30:17 0 0 0 0 0 0 0 27.7 25.69 24.56
09.03.2020 4:31:17 0 0 0 0 0 0 0 27.7 25.86 24.55
09.03.2020 4:32:17 0 0 0 0 0 0 0 27.7 25.77 24.54"

So the real data I want to import is after the red marked part "*Data*". So I was thinking if there is a way to let powerBI search for this part "*Data*" and then start to import the data after this. 

[mahoneypat]

Here is one way to do it.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The first couple steps may not be relevant as I had to copy the text from your post and get it in the right format to show the approach.  This adds a custom column with = if [Column1] = "*Data*" then 1 else null , and then do a Fill down on the new column, and then filter for only 1s to get rid of your header rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLda+MwDMD/FTG4l0GCv53sZXTpysatpazdwVj34LXmLtAkxXV7//5ZTrr7SPJyBAlZ0k9SbL29XT28TicFrOYvlGkC3whPCYGp8QZm5d5u3KZGWXnjPJA8DXFGGAEibhi9oRqDz3bbuF3RnGoPVLCMo/Ox9tadzR7tqT2XW9slXEq2TiBoL0xl/5khdrVucao+rAMqw4euuTXHZVOGQvPlYt2mLRvX1Z3sds4ej2Ci2zW+2Tb7tkXxw9S13XdDkMsUnRsIUGDAQYAEBRoyyDH81Py07tnU3y0knGmVhcTfX6KiZhJTXw6HSypmamBUaJFxJf7bpDKMRaLEDnXpgRGS8OAPksgQ25wI4RYtGTw6RIIk9GsXCMZtAV+C+vzjpqps7S/Ha3zr6/ggxltYl/gSHs3ZPUxQ3aEqYBH0NOr7qGewttUBVsY/2XO4QTxhmT+WpNuRv+6MAMuA6ZQDE6nK+gQbJlSKOiCqj/AxhIuIiD4ixuaSDBE5MJgcQHSaYx8RIcX7kBqHlIwQ7UN6HJJ8bLxsHMpaKO9D+TjE9EgnTgahkC5TlUeo/0Z8aBE6KFMRkn1oaBc6SLfjiav3918=", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Lines.FromText([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type text}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type text}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type text}, {"Custom.16", type text}, {"Custom.17", type text}, {"Custom.18", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "FilterLocation", each if [Custom.1] = "*Data*" then 1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"FilterLocation"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([FilterLocation] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"FilterLocation"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns1",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}, {"State", Int64.Type}, {"FE", Int64.Type}, {"A", Int64.Type}, {"FE_1", Int64.Type}, {"B", Int64.Type}, {"FE_2", Int64.Type}, {"C", Int64.Type}, {"NFE", type number}, {"D", type number}, {"NFE_3", type number}, {"E", type text}, {"NFE_4", type text}, {"F", type text}, {"Temp", type text}, {"SatLevel", type text}, {"Temp_5", type text}})
in
    #"Changed Type2"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat