How to modify the script so that PBI can read new format CSV file ?

[admin11]

Hi All

I have a excel table the data format as below :-

My advance editor script as following :-

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

Now my CSV file format have change to below :-

Can some one advise how to solve my above issue ?

Option 1 :- Start from zero import raw data . 

Pro :- it will work . but all report need to create , since the measure and column are gone.

Option 2 :- modify the script.

Con :- Not sure how to modify the script. 

Hope some one can advise me.

Paul

[amitchandak]

@admin11 , You add remove first 6 rows, using remove rows in home tab of power query