Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DieterD
New Member

Imported web csv data not able to transform

Hello

 

When i import a CSV file from a siemens PLC data log, i have to do this by referer mode. The moment the file is imported i get the data split over columns split with fixed width. This is standard, no changes have been made. 

DieterD_0-1720505957161.png

 

The moment i want to modify this data in the import screen, or afterwords in power BI, every time it instantly gets transformed to 

DieterD_1-1720506070832.png

Is there a setting in de CSV file i am missing or a step i have to do?
The moment i download the csv file to computer and use the file not from the web mode, this problem does not excist.
Downside is the CSV web file get updated every 5-6 minutes with data so would be the one we prefer to use.

Anybody that can send me into the right direction?

Grz Dieter

5 REPLIES 5
Keezz
Helper I
Helper I

Can you send the first few lines of a typical file and the M Code that was generated?

M Code when just imported :
let
Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv&Action=DOWNLOAD", [Headers=[Referer="http://"IP adress"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}})
in
#"Changed Type"

 

M Code after delimiter option
let
Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv&Action=DOWNLOAD", [Headers=[Referer="http://"IP adres"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}})
in
#"Changed Type1"

 

These are the first few lines from a CSV file

SeqNo,Date,UTC Time,Medium,Vehicle,Vehicle Source,Vehicle Type,Vehicle Type Source,Index Select,Buffering Time,Cycle Time [ms],Cycle Status,Last Step,Last Volume 1 Set [ml],Last Volume 1 Actual [ml],Last 1 Volume Tolerance Plus [ml],Last Volume 1 Tolerance Minus [ml],Last Volume 1 MaxMax [ml],Last Volume 2 Set [g],Last Volume 2 Actual [g],Last 2 Volume Tolerance Plus [g],Last Volume 2 Tolerance Minus [g],Last Volume 2 MaxMax [g],Evacuation Vacuumpipe Set  [x0.1 mbar],Evacuation Vacuumpippe Actual  [x0.1 mbar],System Test Pressure Set [mbar],System Test Pressure Actual [mbar],Compressed Air Fill Set [mbar],Compressed Air Fill Actual [mbar],Compressed Air Stabilize Set [mbar],Compressed Air Stabilize Actual [mbar],Compressed Air Test Set [mbar],Compressed Air Test Delta Set [mbar],Compressed Air Test Actual [mbar],Compressed Air Test Delta Actual [mbar],Compressed Air Relive 1 Set [mbar],Compressed Air Relive 1 Actual [mbar],Compressed Air Relive 2 Set [mbar],Compressed Air Relive 2 Actual [mbar],Test Schrader Valve 1 Set  [x0.1 mbar],Test Schrader Valve 1 Actual  [x0.1 mbar],Evacuation 1 Set  [x0.1 mbar],Evacuation 1 Actual [x0.1 mbar],Evacuation 2 Set  [x0.1 mbar],Evacuation 2 Actual  [x0.1 mbar],Evacuation Stabilize Set  [x0.1 mbar],Evacuation Stabilize Actual  [x0.1 mbar],Vacuum Test Set  [x0.1 mbar],Vacuum Test Delta Set  [x0.1 mbar],Vacuum Test Actual [x0.1 mbar],Vacuum Test Delta Actual [x0.1 mbar],Evacuation 3 Actual [x0.1 mbar],Filling Pressure Set [mbar],Filling Pressure Actual [mbar],Filling Stabilisation Pressure Set  [mbar],Filling Stabilisation Pressure Actual  [mbar],Fluid Pressure Test Set [mbar],Fluid Pressure Test Delta Set [mbar],Fluid Pressure Test Actual [mbar],Fluid Pressure Test Delta Actual [mbar],Levelling Time Actual [ms],                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
      40001,2024-07-04,08:17:06.860,"ACL                 ","BB   482501         ",     3,"1                   ",     0,     1,"04.07.24  10:17:06",      64609,     1,    62,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0, 14000,  9824, 14900, 16077,     0, 16049, 14500,     0, 16046,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,          0
      40002,2024-07-04,08:22:43.896,"R1234yf             ","BB   482490         ",     3,"1                   ",     0,     1,"04.07.24  10:22:43",     187610,     1,    62,   700,     0,     0,     0,     0,   650,   656,    25,    20,   725,   100,    35,     0,     0, 16051, 16452, 16051, 14805,     0, -2000, 15639,  -412,  3000,  2972,  1300,  1037,   250,  1195,   600,   581,    30,    17,     0,    66,     0,    40,    72,     6,    18,     0,     0,     0,     0,     0,     0,     0,     0,          0
      40003,2024-07-04,08:28:26.508,"ACL                 ","BB   482505         ",     3,"1                   ",     0,     1,"04.07.24  10:28:26",      64601,     1,    62,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0, 14000, 10175, 14900, 16085,     0, 16057, 14500,     0, 16053,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,          0
      40004,2024-07-04,08:30:43.255,"R1234yf             ","BB   482492         ",     3,"1                   ",     0,     1,"04.07.24  10:30:42",     180081,     1,    62,   700,     0,     0,     0,     0,   650,   659,    25,    20,   725,   100,    39,     0,     0, 16039, 16438, 16039, 15739,     0, -2000, 15769,  -270,  3000,  2964,  1300,  1031,   250,  1195,   600,   596,    30,    15,     0,    49,     0,    40,    54,     5,    15,     0,     0,     0,     0,     0,     0,     0,     0,          0
      40005,2024-07-04,08:39:04.916,"ACL                 ","BB   482507         ",     3,"1                   ",     0,     1,"04.07.24  10:39:04",      64624,     1,    62,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0, 14000, 10369, 14900, 16076,     0, 16048, 14500,     0, 16045,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,     0,          0

 

 

Thanks. The example file does not look very special.

 

And I was wondering: Do you want the file in 4 fixed columns to start with? And how did you get the second query?

Below the query if you just want all columns in the file as they are in the CSV file. You can refer to this querey for further processing...

 

If you just want the 4 fixed columns, then just forget about the second query.

 

let
    Source = Csv.Document(
        Web.Contents(
            "http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv&Action=DOWNLOAD"
            , [Headers=[Referer="http://"IP adress"/Portal/Portal.mwsl?PriNav=DataLogs"]]
            )
        , null  // defaults to ","
        , null // or replace by the number of colums you are interested in. // was {0, 117, 164, 185, 206}
        , ExtraValues.Ignore // ignores columns you are not interested in (if any)
        ,1252  // Is the windows Encoding, I assume it is correct for your data
        ),
    #"First Row as Headers" = Table.PromoteHeaders(Source) // was: #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}})
in
    #"First Row as Headers"

 

Sorry, I did not look very well. The second screenshot does not correspond to the second query.
The screenshot shows the HTML source of the web page by the look of it.
Is that your problem or is it (as I thought) the formatting of the data??

Hi @DieterD 

Based on the code you have offered, your url should be the path that the csv file located. 

it sholuld be "https://..../ACF2.csv", you can try the following

 

Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv", [Headers=[Referer="http://"IP adress"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252)

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors