Power BI does not detect new columns if source file took from folder

[Sohaib]

Hi,

      I have created a dashboard and encounter some issues when changes done in source file. I have added new columns in excel file xlxs. But new columns not showing in Power BI transmormation window not either in report view. I setup source file through folder as my dashboard model will require it to update through new file which can be just put into the specific folder and it will automatically transform and merged all files. So the issue is only that when i add the new column in my xlxs file it wont appear in the power Bi data.

= Csv.Document(File.Contents("filepath"),[Delimiter=";", Columns=17, Encoding=612312

= Excel.Workbook(File.Contents("filepath"), null, true)

I know that above code is only work when the source is direct xlxs file or csv file but in case of folder this code not appaer in advance editor. Please help...

[AnalyticsWizard · Accepted solution]

@Sohaib 

It seems you’re encountering an issue with Power BI where newly added columns in your Excel file (.xlsx) are not showing up in the Power Query Editor. Let’s address this:

1. Calculated Columns in Power Query vs. DAX:

   • When you create a calculated column directly within the Power BI Data Model (using DAX), it won’t automatically appear in the Power Query Editor.
   • Calculated columns created in DAX (the “front end” part) do not transfer to Power Query.
   • To make your new column visible in Power Query, you need to create it within Power Query itself.

2. Creating a New Column in Power Query:

   • Open your Power Query Editor.
   • After loading a table, you have two options:
     • Go back to Power Query Editor (M language): If you use this option, the new column will be visible both in the Power Query Editor (before loading) and in the Data view (after loading).
     • Go to Data view (DAX language): Create the new column using Power Query’s language called “M.” Note that M has a different syntax than DAX.
   • Here are some discussions related to this matter:
     • Columns are not visible in Power Query
     • Why won’t a new column added to a table show up in Power Query?
     • Is there a way to make calculated columns show in Power Query?

3. Advanced Editor for Folder Sources:

   • You mentioned that your source file is set up through a folder. Unfortunately, the code you provided (Csv.Document and Excel.Workbook) works only for direct .xlsx files or CSV files.
   • In the case of a folder source, you won’t find this code in the Advanced Editor.
   • Consider creating the new column directly within Power Query using the appropriate language (M).

I hope this helps! If you have any further questions, feel free to ask. 😊

[Sohaib]

thanks for the reply @Tom_Y but already mentioned that your provided solution works only when we source the direct xlxs or csv file but my case is different I am using source as a folder.

[Tom_Y]

I have that problem before, try

Columns=17 change it to Columns=null