cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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

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.

admin11_0-1611190437875.png

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

admin11_1-1611190864313.png

 

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 :-

 

admin11_2-1611191037592.png

 

when i click go to error :-

admin11_3-1611191081398.png

 

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 :-

admin11_4-1611191279603.png

 

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

 

Above is my PBI file :-

Paul Yeo

1 ACCEPTED SOLUTION
Super User II
Super User II

@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.

 

negi007_0-1611493797094.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

2 REPLIES 2
Super User II
Super User II

@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.

 

negi007_0-1611493797094.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors