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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ksab23
Helper I
Helper I

New Column in source file not updating in powerbi

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?

1 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! 

View solution in original post

6 REPLIES 6
mlsx4
Super User
Super User

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.

@ksab23 

Have you tried this?

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
Solution Sage
Solution Sage

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

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.

ksab23_0-1692704961267.png

 



Hi @ksab23 

 

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

 

JoeBarry_0-1692706301146.png

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

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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