Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admin11
Memorable Member
Memorable Member

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
negi007
Community Champion
Community Champion

@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 on linkedin

View solution in original post

2 REPLIES 2
negi007
Community Champion
Community Champion

@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 on linkedin

Paulyeo11
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.