How to convert M code from load by folder to sharepoint folder ?

[Paulyeo11]

Hi All

I have create a PBI file using folder load it working fine , now i like to change the reload using sharepoint. instead of create the new PBI file , i just like to figure out how to change the M code. 

I have created one PBI file using below path it working fine , i am able to reload after i enter ID and PW at edit premission  :-

https://isdnholdings.sharepoint.com/sites/TDSGroup/

My existing PBI file using folder load from one drive the M Code :-

let
Source = Folder.Files("C:\Users\pauly\OneDrive - ISDN Holdings Limited\PBI FOLDER\SI_TDS"),

Now i try to replace above with :-

let
Source = Folder.Files("https://isdnholdings.sharepoint.com/sites/TDSGroup/"),

I get erro code :-

DataFormat.Error: The supplied folder path must be a valid absolute path.
Details:
https://isdnholdings.sharepoint.com/sites/TDSGroup/

Hope some one can advise me.

Paul Yeo

[amitchandak]

@Paulyeo11 , In a new file create source a Sharepoint folder : https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

Then open edit query right click on the table and open Advance editor and copy this code (till connection details) . Go to the original file and replace the same and check 

[Paulyeo11]

Hi Amit

I have below 2 set of M code :-

(1) load by folder M code :-

Source = Folder.Files("C:\Users\pauly\OneDrive - ISDN Holdings Limited\PBI FOLDER\SI_TDS"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"CUST_ID", type text}, {"COMPANY", type text}, {"inv", Int64.Type}, {"date", type date}, {"SAL", type text}, {"Sal1", type text}, {"SEG", type text}, {"SEG_SUB", type text}, {"Area", type text}, {"Pclass", Int64.Type}, {"Dept", Int64.Type}, {"quantity", Int64.Type}, {"cost", type number}, {"sales", type number}, {"PRODUCT_CODE", type text}, {"Desc1", type text}, {"Scode", type text}, {"PRO_CLASS", type text}, {"sign", Int64.Type}, {"Cur", type text}, {"Add", type text}, {"BRAND_C", type text}, {"SAL_C", type text}, {"G_TYPE", Int64.Type}, {"Date1", type date}, {"", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"inv", "INV"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Desc1", "Desc1 - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Desc1 - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Desc1 - Copy.1", "Desc1 - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Desc1 - Copy.1", type text}, {"Desc1 - Copy.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Desc1 - Copy.1", "BRAND"}, {"sales", "sales"}, {"Desc1", "MODEL"}, {"Scode", "CUST_RATING"}, {"Area", "country"}})
in
#"Renamed Columns2"

(2) load by sharepoint tested okay :-

Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/TDSGroup/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "SI_TA.csv" or [Name] = "SI_TS.csv")),
#"SI_TA csv_https://isdnholdings sharepoint com/sites/TDSGroup/Shared Documents/PBI FOLDER/SALES INVOICE TDS/" = #"Filtered Rows"{[Name="SI_TA.csv",#"Folder Path"="https://isdnholdings.sharepoint.com/sites/TDSGroup/Shared Documents/PBI FOLDER/SALES INVOICE TDS/"]}[Content],
#"Imported CSV" = Csv.Document(#"SI_TA csv_https://isdnholdings sharepoint com/sites/TDSGroup/Shared Documents/PBI FOLDER/SALES INVOICE TDS/",[Delimiter=",", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CUST_ID", type text}, {"COMPANY", type text}, {"inv", Int64.Type}, {"date", type date}, {"SAL", type text}, {"Sal1", type text}, {"SEG", type text}, {"SEG_SUB", Int64.Type}, {"Area", type text}, {"Pclass", Int64.Type}, {"Dept", Int64.Type}, {"quantity", Int64.Type}, {"cost", type number}, {"sales", type number}, {"PRODUCT_CODE", type text}, {"Desc1", type text}, {"Scode", type text}, {"PRO_CLASS", type text}, {"sign", Int64.Type}, {"Cur", type text}, {"Add", type text}, {"BRAND_C", type text}, {"SAL_C", type text}, {"G_TYPE", Int64.Type}, {"Date1", type date}, {"", type text}})
in
#"Changed Type"

Can you advise me how to convert the Script 1 to load by sharepoint ?

Paul