cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
génélec
New Member

Problem updating simple data in Power Query

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).

image.png
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

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

View solution in original post

8 REPLIES 8
v-stephen-msft
Community Support
Community Support

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.

20.png

 

 

 

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.

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

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

Can you paste the M code from the Query Editor?

Thanks,

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:

génélec_8-1620749221965.png

 

génélec_1-1620749062443.pnggénélec_2-1620749092603.pnggénélec_3-1620749108053.png

 

génélec_4-1620749126411.png

 

génélec_5-1620749143921.pnggénélec_6-1620749158466.pnggénélec_7-1620749173186.png

Thanks a lot for helping me

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...

 

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

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"

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors