Cells Uploading without Values

[dcadwallader]

Afternoon,

I have a Power Bi report linked to an excel file.

Upon updating refreshing, all values update and refresh as one would expect them to.

Except for a series of columns titled within the excel file by number (1, 2, 3 etc...).

The values within these cells do not upload - see attached screenshots.

I have tried uploading and refreshing this excel file to a different Power Bi file and they appear fine.

However, I'm hoping there's a more elegant solution to my problem than deleting and starting again - as I have a large number of Calcs now included in the file.

Thoughts?

[jennratten]

Hello - FYI, scenarios like this are often caused by explicitly naming column names where it may not be necessary.  I recommend you delete the #"Changed Types" step that Power Query added automatically and instead, assign the column types later on in the script.

[v-angzheng-msft]

Hi, @dcadwallader 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcadwallader]

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}}),

[KNP]

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

[dcadwallader · Accepted solution]

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]

There's not enough information.

Can you share the M code for the query that connects to the excel file.

Go to 'Transform data' >> select the query on the left that connects to the Excel file >> go to 'Advanced editor' >> copy and paste that code back here (make sure to change any sensitive detail if there is any).