New Column in source file not updating in powerbi

[ksab23]

Hi all, 

I have what seems to be a common problem but no solutions worked for me so far. I have added a column to my .xlsx source file and I can see it in Power Query but not in PBI. 

My .xlsx file is linked from the cloud, as many users need to have access to it. 

What I tried so far:

- refresh (query and pbi)

- rename the column in query

- checked Advanced Editor but couldn't find data that other solutions refer to (e.g. [Delimiter=",", Columns=10, etc.]), see below

let
Source = Excel.Workbook(Web.Contents("https://FILE%NAME%AND%PATH.xlsx"), null, true),
#"SSG Sales Training Data_Sheet" = Source{[Item="FILE_NAME_AND_PATH.Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"FILE_NAME_AND_PATH.DataSheet", [PromoteAllScalars=true]),
etc.

Any ideas on what to try next?

[mlsx4]

Hi @ksab23 

May sound weird but, what if you format your excel sheet as a table? I think it is the best way to manage Excel as data source.

[mlsx4]

@ksab23 

Have you tried this?

[ksab23 · Accepted solution]

I did but it didn't do anything. However, I tried to retrace 'Applied Steps' and in 'Promoted headers' you have two options - Promote all Scalar types and Promote only Text and Number types. I switched to 'only text and number', and nothing happened but upon switching back to 'scalar' it did the trick! 

[JoeBarry]

Hi @ksab23 

This information can be found in the Transform File of the Excel. Locate this file in Power Query and open Advanced Editor and change the column amount

Thanks

Joe

[ksab23]

I am not sure I follow. I can get to Advanced Editor but I don't see any fields that I could edit and they would actually affect my sheet. Screenshot below.

[JoeBarry]

Hi @ksab23 

It seems this is only available when importing an Excel from the SharePoint Folder Connector and not web

Another work around is to import the Excel again keeping the original import. Check if the new columns are appearing.

With the new import, you can copy the Query in Advanced Editor and then open the old import's advanced editor the replace and copy the new query in there. If you have made transformations to the original data, then just copy the source section of the new import and replace the old source.

Thanks

Joe