Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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!
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.
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!
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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
97 | |
41 | |
38 |
User | Count |
---|---|
151 | |
123 | |
79 | |
73 | |
71 |