Forum Discussion
Cells Uploading without Values
- 4 years ago
Took a rather inelegant approach but have a solution in place.
I've simply added the same file as another extract on the file - it uploads the data fine on the second link and I got to keep all the calcs I'd put in the same link.
Probably going to cause me a headache down the road, linking to the same file twice in the same Bi Report but sure, that's future me's problem!
Cheers for the assistance guys!
KNP,
Thanks for getting in touch - for context the troublesome columns are columns 207 through 274.
let
Source = Excel.Workbook(File.Contents("filename.xlsx"), null, true),
EXTRACT_Sheet = Source{[Item="EXTRACT",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(EXTRACT_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type text}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type text}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type text}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type text}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type text}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type text}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type text}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type text}, {"Column62", type any}, {"Column63", type text}, {"Column64", type text}, {"Column65", type any}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type any}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type any}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type any}, {"Column102", type any}, {"Column103", type text}, {"Column104", type any}, {"Column105", type text}, {"Column106", type any}, {"Column107", type any}, {"Column108", type any}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type text}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", type any}, {"Column130", type text}, {"Column131", type text}, {"Column132", type any}, {"Column133", type text}, {"Column134", type text}, {"Column135", type any}, {"Column136", type text}, {"Column137", type any}, {"Column138", type text}, {"Column139", type text}, {"Column140", type text}, {"Column141", type text}, {"Column142", type text}, {"Column143", type text}, {"Column144", type text}, {"Column145", type text}, {"Column146", type text}, {"Column147", type text}, {"Column148", type text}, {"Column149", type text}, {"Column150", type text}, {"Column151", type text}, {"Column152", type text}, {"Column153", type text}, {"Column154", type text}, {"Column155", type text}, {"Column156", type text}, {"Column157", type text}, {"Column158", type text}, {"Column159", type text}, {"Column160", type text}, {"Column161", type text}, {"Column162", type text}, {"Column163", type text}, {"Column164", type text}, {"Column165", type text}, {"Column166", type text}, {"Column167", type text}, {"Column168", type text}, {"Column169", type text}, {"Column170", type text}, {"Column171", type text}, {"Column172", type text}, {"Column173", type text}, {"Column174", type text}, {"Column175", type text}, {"Column176", type text}, {"Column177", type text}, {"Column178", type text}, {"Column179", type text}, {"Column180", type text}, {"Column181", type text}, {"Column182", type text}, {"Column183", type text}, {"Column184", type text}, {"Column185", type text}, {"Column186", type text}, {"Column187", type text}, {"Column188", type text}, {"Column189", type text}, {"Column190", type text}, {"Column191", type text}, {"Column192", type text}, {"Column193", type text}, {"Column194", type text}, {"Column195", type text}, {"Column196", type text}, {"Column197", type text}, {"Column198", type text}, {"Column199", type text}, {"Column200", type text}, {"Column201", type text}, {"Column202", type text}, {"Column203", type text}, {"Column204", type text}, {"Column205", type text}, {"Column206", type text}, {"Column207", Int64.Type}, {"Column208", Int64.Type}, {"Column209", Int64.Type}, {"Column210", Int64.Type}, {"Column211", Int64.Type}, {"Column212", Int64.Type}, {"Column213", Int64.Type}, {"Column214", Int64.Type}, {"Column215", Int64.Type}, {"Column216", Int64.Type}, {"Column217", Int64.Type}, {"Column218", Int64.Type}, {"Column219", Int64.Type}, {"Column220", Int64.Type}, {"Column221", Int64.Type}, {"Column222", Int64.Type}, {"Column223", Int64.Type}, {"Column224", Int64.Type}, {"Column225", Int64.Type}, {"Column226", Int64.Type}, {"Column227", Int64.Type}, {"Column228", Int64.Type}, {"Column229", Int64.Type}, {"Column230", Int64.Type}, {"Column231", Int64.Type}, {"Column232", Int64.Type}, {"Column233", Int64.Type}, {"Column234", Int64.Type}, {"Column235", Int64.Type}, {"Column236", Int64.Type}, {"Column237", Int64.Type}, {"Column238", Int64.Type}, {"Column239", Int64.Type}, {"Column240", Int64.Type}, {"Column241", Int64.Type}, {"Column242", Int64.Type}, {"Column243", Int64.Type}, {"Column244", Int64.Type}, {"Column245", Int64.Type}, {"Column246", Int64.Type}, {"Column247", Int64.Type}, {"Column248", Int64.Type}, {"Column249", Int64.Type}, {"Column250", Int64.Type}, {"Column251", Int64.Type}, {"Column252", Int64.Type}, {"Column253", Int64.Type}, {"Column254", Int64.Type}, {"Column255", Int64.Type}, {"Column256", Int64.Type}, {"Column257", Int64.Type}, {"Column258", Int64.Type}, {"Column259", Int64.Type}, {"Column260", Int64.Type}, {"Column261", Int64.Type}, {"Column262", Int64.Type}, {"Column263", Int64.Type}, {"Column264", Int64.Type}, {"Column265", Int64.Type}, {"Column266", Int64.Type}, {"Column267", Int64.Type}, {"Column268", Int64.Type}, {"Column269", Int64.Type}, {"Column270", Int64.Type}, {"Column271", Int64.Type}, {"Column272", Int64.Type}, {"Column273", Int64.Type}, {"Column274", Int64.Type}, {"Column275", type text}, {"Column276", type text}}),
dcadwallader - that's a very interesting file you have, just a handful of columns. 😲
Would you be able to share a link to a sample of that file (change any sensitive data) so I can see if I can reproduce the issue? If you just leave a few hundred rows in the sample but make sure you keep all columns.
- dcadwallader4 years agoHelper I
Took a rather inelegant approach but have a solution in place.
I've simply added the same file as another extract on the file - it uploads the data fine on the second link and I got to keep all the calcs I'd put in the same link.
Probably going to cause me a headache down the road, linking to the same file twice in the same Bi Report but sure, that's future me's problem!
Cheers for the assistance guys!