How to know which line of script which handle first row of row data ?

[admin11]

Hi All

My row data first row fill with wording CRM_TS: 

My script below :-

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FT_CRM", [ApiVersion = 15]),
#"CRM_TS csv_https://isdnholdings sharepoint com/sites/FT_CRM/Shared Documents/" = Source{[Name="CRM_TS.csv",#"Folder Path"="https://isdnholdings.sharepoint.com/sites/FT_CRM/Shared Documents/"]}[Content],
#"Imported CSV" = Csv.Document(#"CRM_TS csv_https://isdnholdings sharepoint com/sites/FT_CRM/Shared Documents/",[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"CRM_TS :", type text}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}, {"_5", type text}, {"_6", type text}, {"_7", type text}, {"_8", type text}, {"_9", type text}, {"_10", type text}, {"_11", type text}, {"_12", type text}, {"_13", type text}, {"_14", type text}}),
#"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Split Column by Positions" = Table.SplitColumn(#"Promoted Headers1", "Amount", Splitter.SplitTextByPositions({0, 2}), {"Amount.1", "Amount.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Amount.1", type text}, {"Amount.2", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Amount.2", "Amount"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Closing Date", type date}})
in
#"Changed Type3" // GOOD

I like to know above script how to modify , so that it will not reading first row ?

Paul

[AllisonKennedy · Accepted solution]

@admin11  It looks like your script is already doing this by promoting the headers - note in the 'Applied Steps' on the right hand side how you have a list of the steps, you'll see 'Promoted Headers' and 'Promoted Headers1'. This ensures the first row is removed essentially. 

You could tidy up the code using other methods, but yours works. There is a Remove Rows > Remove Top Rows option in Power Query ribbon that you could use instead of the first promoted headers in your list.

You can also delete the 'Changed Type' step from your applied steps as you don't yet care about data type until you remove that first row (NOTE Keep the Changed Type1 step, you need this.

[admin11]

@AllisonKennedy 

Thank you very much , it work fine now. 

first i delete :-

#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

then i get another error , i delete :-

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"CRM_TS :", type text}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}, {"_5", type text}, {"_6", type text}, {"_7", type text}, {"_8", type text}, {"_9", type text}, {"_10", type text}, {"_11", type text}, {"_12", type text}, {"_13", type text}, {"_14", type text}}),

now work fine 

Paul