Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@leisay , if it fixed number ? refer
https://www.excelguru.ca/blog/2019/12/09/remove-dynamic-number-of-top-rows/
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |