what i need to change M code when i need to change the file format from XLS to XLSX ?

[admin11]

Hi All

i found out that PBI don't allow me to mix CSV XLS and XLSX format in one folder , when i refresh on demand it will have error , even i have install 64 bit exe file , i still cannot solve the problem.

Below is my M Code :-

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/F_T_INPUT/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "COMPETITOR.xls")),
#"COMPETITOR xls_https://isdnholdings sharepoint com/sites/F_T_INPUT/Shared Documents/" = #"Filtered Rows"{[Name="COMPETITOR.xls",#"Folder Path"="https://isdnholdings.sharepoint.com/sites/F_T_INPUT/Shared Documents/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"COMPETITOR xls_https://isdnholdings sharepoint com/sites/F_T_INPUT/Shared Documents/"),
COMPETITOR = #"Imported Excel"{[Name="COMPETITOR"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(COMPETITOR, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"COMPETITOR", type text}, {"COMPETITOR_FLAG", type text}, {"date_t", type date}, {"Column4", type datetime}, {"local amt", type text}, {"exchange rate", type number}, {"REVENUE_C", type text}, {"local amt_1", Int64.Type}, {"exchange rate_2", type text}, {"NET_PROFIT_A_TAX_C", type text}, {"NPBT %", Percentage.Type}, {"local amt_3", Int64.Type}, {"exchange rate_4", type text}, {"EXP", Int64.Type}, {"GROSS PROFIT", type text}, {"GP %", type text}, {"date_t_5", type date}, {"Column18", type text}, {"Column19", type text}, {"local amt_6", type text}, {"exchange rate_7", type text}, {"SHARE_HOLDER_FUND_C", Int64.Type}, {"local amt_8", type text}, {"exchange rate_9", type text}, {"TOTAL_QUITY", Int64.Type}, {"local amt_10", type text}, {"exchange rate_11", type text}, {"SHARE_CAPITAL", Int64.Type}, {"local amt_12", type text}, {"exchange rate_13", type text}, {"RETAINED_EARN", Int64.Type}, {"CURRENT_PRICE", type text}, {"NO_OF_SHARE", type text}, {"MKT_CAP", type text}, {"ASSET_OVER_NO_SHARE", type text}, {"PE", type text}, {"EPS", type text}, {"local amt_14", Int64.Type}, {"exchange rate_15", type number}, {"TOTAL_ASSETS_C", type text}, {"local amt_16", type text}, {"exchange rate_17", type text}, {"TOTAL_CURRENT_ASSET_C", type text}, {"local amt_18", Int64.Type}, {"exchange rate_19", type number}, {"TOTAL_LIABILITIES_C", Int64.Type}, {"local amt_20", Int64.Type}, {"exchange rate_21", type number}, {"CURRENT_LIABILITIES_C", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date_t", type date}}, "en-US"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"REVENUE_C", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"REVENUE_C", "AMT REVENUE_C"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"NET_PROFIT_A_TAX_C", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"NET_PROFIT_A_TAX_C", "AMT NET_PROFIT_A_TAX_C"}}),
#"Kept First Rows" = Table.FirstN(#"Renamed Columns1",10)
in
#"Kept First Rows"

Hope some one can advise me. 

Remark :- i will open the competitor.xls file and save as competitor.xlsx

i have change 2 location on below M Code from xls to xlsx :-

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/F_T_INPUT/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "COMPETITOR.xlsx")),
#"COMPETITOR xls_https://isdnholdings sharepoint com/sites/F_T_INPUT/Shared Documents/" = #"Filtered Rows"{[Name="COMPETITOR.xlsx",#"Folder Path"="https://isdnholdings.sharepoint.com/sites/F_T_INPUT/Shared Documents/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"COMPETITOR xls_https://isdnholdings sharepoint com/sites/F_T_INPUT/Shared Documents/"),
COMPETITOR = #"Imported Excel"{[Name="COMPETITOR"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(COMPETITOR, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"COMPETITOR", type text}, {"COMPETITOR_FLAG", type text}, {"date_t", type date}, {"Column4", type datetime}, {"local amt", type text}, {"exchange rate", type number}, {"REVENUE_C", type text}, {"local amt_1", Int64.Type}, {"exchange rate_2", type text}, {"NET_PROFIT_A_TAX_C", type text}, {"NPBT %", Percentage.Type}, {"local amt_3", Int64.Type}, {"exchange rate_4", type text}, {"EXP", Int64.Type}, {"GROSS PROFIT", type text}, {"GP %", type text}, {"date_t_5", type date}, {"Column18", type text}, {"Column19", type text}, {"local amt_6", type text}, {"exchange rate_7", type text}, {"SHARE_HOLDER_FUND_C", Int64.Type}, {"local amt_8", type text}, {"exchange rate_9", type text}, {"TOTAL_QUITY", Int64.Type}, {"local amt_10", type text}, {"exchange rate_11", type text}, {"SHARE_CAPITAL", Int64.Type}, {"local amt_12", type text}, {"exchange rate_13", type text}, {"RETAINED_EARN", Int64.Type}, {"CURRENT_PRICE", type text}, {"NO_OF_SHARE", type text}, {"MKT_CAP", type text}, {"ASSET_OVER_NO_SHARE", type text}, {"PE", type text}, {"EPS", type text}, {"local amt_14", Int64.Type}, {"exchange rate_15", type number}, {"TOTAL_ASSETS_C", type text}, {"local amt_16", type text}, {"exchange rate_17", type text}, {"TOTAL_CURRENT_ASSET_C", type text}, {"local amt_18", Int64.Type}, {"exchange rate_19", type number}, {"TOTAL_LIABILITIES_C", Int64.Type}, {"local amt_20", Int64.Type}, {"exchange rate_21", type number}, {"CURRENT_LIABILITIES_C", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date_t", type date}}, "en-US"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"REVENUE_C", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"REVENUE_C", "AMT REVENUE_C"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"NET_PROFIT_A_TAX_C", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"NET_PROFIT_A_TAX_C", "AMT NET_PROFIT_A_TAX_C"}}),
#"Kept First Rows" = Table.FirstN(#"Renamed Columns1",10)
in
#"Kept First Rows"

I get below error :-

when i click go to error :-

what are the next thing i need to do in order to clear the error ? As now PQ shown i error on table , see image below :, mean it does not load the table to PQ :-

https://www.dropbox.com/s/kbxfnrir8c5rp9q/PBT_V2021_116%20change%20competitor%20file%20name%20XLSX%2...

Above is my PBI file :-

Paul Yeo

[negi007 · Accepted solution]

@admin11 I think you wish to change the data source from your xls to xlsx file. You can simply do it inthe power bi desktop window.  In your powerbi, select transform data ->Data source settings -> Change source -> select your new file with xlsx extension. No need to modify your Powerquery (M) code.

[Paulyeo11]

Thank you for sharing . i have start create from zero. how i wish you reply me faster . thank you any way.