Problem updating simple data in Power Query

[génélec]

Hello the forum,

I am a new user of PowerBI pro and I have a problem to solve:

From Power Query I import data with an Excel file as a source (see screenshot of the Excel).

When I add a new column in my Excel (add new month values, May for example) and refresh, Power Query does not download this new column.
On the other hand, if I recreate a new source using the same link, the new column does appear.

Can you guide me to fix this problem? Thank you !

I am available to clarify my problem or present it via Teams

[v-stephen-msft]

Hi @génélec ,

Could you tell me if your problem has been solved?

If it is, kindly Accept it as the solution. More people will benefit from it.

Or allow me to ask a question that seems stupid, after you refreshed it in Power Query, did you try to click the previous step in Applied steps? Maybe because of some steps, the new column was removed or hidden.

Best Regards,

Stephen Tao

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

[génélec]

Hello Stephen,

Thanks for answering me,

As described below, I tried to delete steps and refresh. The problem occurs when I developp datas, then even if I refresh, the new columns of my Excel file don't appear.

If I remove the first step (data expand) and do it again (add step data expand), my new columns now appears..

What is the parameter which can influence that ? What should I do for PowerQuery to recognize my new Excel columns ?

EDIT: Using blopez11 M code, now it works. Thanks for the inquiry

[blopez11]

Is it an Excel file (.xlsx) or a CSV file?

Can you paste the M code from the Query Editor?

Thanks,

[génélec]

Hello blopez11,

Thanks for answering me.

It is an Excel file (.xlsx) and here is the M code from the Query Editor:

let
Source = Excel.Workbook(File.Contents("C:\xxxx\xxxxxx\xxxxxx\xxxx.xlsx"), null, true),
#"Data développé" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30"}),
#"En-têtes promus" = Table.PromoteHeaders(#"Data développé", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"link PointCode", type text}, {"Column2", type text}, {"PointCode", type text}, {"01/01/2019", Int64.Type}, {"01/02/2019", Int64.Type}, {"01/03/2019", Int64.Type}, {"01/04/2019", Int64.Type}, {"01/05/2019", Int64.Type}, {"01/06/2019", Int64.Type}, {"01/07/2019", Int64.Type}, {"01/08/2019", Int64.Type}, {"01/09/2019", Int64.Type}, {"01/10/2019", Int64.Type}, {"01/11/2019", Int64.Type}, {"01/12/2019", Int64.Type}, {"01/01/2020", Int64.Type}, {"01/02/2020", Int64.Type}, {"01/03/2020", Int64.Type}, {"01/04/2020", Int64.Type}, {"01/05/2020", type number}, {"01/06/2020", type number}, {"01/07/2020", type number}, {"01/08/2020", type number}, {"01/09/2020", type number}, {"01/10/2020", type number}, {"01/11/2020", type number}, {"01/12/2020", type number}, {"01/01/2021", type number}, {"01/02/2021", type number}, {"01/03/2021", type number}, {"01/04/2021", type number}, {"link PointCode_1", type text}, {"Sheet", type text}, {"false", type logical}}),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Type modifié", {"false", "Sheet", "link PointCode_1", "link PointCode", "PointCode", "Column2"}, "Attribut", "Valeur"),
#"Type modifié1" = Table.TransformColumnTypes(#"Supprimer le tableau croisé dynamique des autres colonnes",{{"Attribut", type date}}),
#"Lignes triées" = Table.Sort(#"Type modifié1",{{"Attribut", Order.Descending}})
in
#"Lignes triées"

I made some screenshots of it if you prefer:

Thanks a lot for helping me

[blopez11]

It is because the auto-generated expandtablecolumn hard codes the column names

Please take a look at the following post and hopefuly it can help you overcome this

https://eriksvensen.wordpress.com/2019/10/05/powerquery-control-the-expand-columns-so-it-includes-ne...

[génélec]

I didn't really understood what he has done, what should I change in the M code ?

[blopez11 · Accepted solution]

Try the below, since I don't have the data, I didn't test it

I referred to the link above and the one below for reference

Good luck,

https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-a...

let
Source = Excel.Workbook(File.Contents("C:\xxxx\xxxxxx\xxxxxx\xxxx.xlsx"), null, true),
#"Data développé" = Table.ExpandTableColumn(Source, "Data", Table.ColumnNames (Source[Data]{0})),
#"En-têtes promus" = Table.PromoteHeaders(#"Data développé", [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnType (#"En-têtes promus",
List.Transform (
Table.ColumnNames (#"En-têtes promus"),
each {_, type text}
)
),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Type modifié", {"false", "Sheet", "link PointCode_1", "link PointCode", "PointCode", "Column2"}, "Attribut", "Valeur"),
// Here you can transform any other columns that need to be transformed
#"Type modifié1" = Table.TransformColumnTypes(#"Supprimer le tableau croisé dynamique des autres colonnes",{{"Attribut", type date}}),
#"Lignes triées" = Table.Sort(#"Type modifié1",{{"Attribut", Order.Descending}})
in
#"Lignes triées"

[génélec]

Hello,

I tried the code but it doesn't work.

The step which blocks a new column adding in PowerQuery is the first one, when I expand datas then add a new column on my Excel file and refresh --> it doesn't work.

Now if I remove this step (expand datas) and I do it again (no need of refreshing) I have the new column.

I think that you are right : PowerQuery takes into account only the present columns when datas are developped but the fact is that before 2 weeks ago, I didn't have this issue and I changed nothing !

EDIT : With changing only the first step code

#"Data développé" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30"}),

by

#"Data développé" = Table.ExpandTableColumn(Source, "Data", Table.ColumnNames (Source[Data]{0}))

Now it works correctly. Thanks a lot for solving my issue.