Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 :-
Above is my PBI file :-
Paul Yeo
Solved! Go to 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.
Proud to be a Super User!
@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.
Proud to be a Super User!
Thank you for sharing . i have start create from zero. how i wish you reply me faster . thank you any way.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |