Power Query refreshing issue?

[lambrosx]

Hi,

I try to estimate price variation on every item (article_id). I started with this link. Everything goes well in Power Query but not when refreshing  the query in excel.  [PU HT N-1] stops displaying after few lines and of course my calculation [Variation] too. 

I tried to copy this in Power BI and everything is ok too.

let
    Source = Ligne,
    #"Requêtes fusionnées" = Table.NestedJoin(Source, {"PIECE_ID"}, Vente, {"ID"}, "Vente", JoinKind.LeftOuter),
    #"Vente développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Vente", {"Date", "Client", "Référence"}, {"Vente.Date", "Vente.Client", "Vente.Référence"}),
    #"Duplication de la colonne" = Table.DuplicateColumn(#"Vente développé", "Vente.Date", "Vente.Date - Copier"),
    #"Année extraite" = Table.TransformColumns(#"Duplication de la colonne",{{"Vente.Date", Date.Year, Int64.Type}}),
    #"Lignes filtrées" = Table.SelectRows(#"Année extraite", each [PU HT] > 0),
    #"Lignes groupées" = Table.Group(#"Lignes filtrées", {"ARTICLE_ID", "Vente.Date", "Vente.Client", "Vente.Référence", "PU HT"}, {{"PU HT.1", each _, type table [PIECE_ID=number, ARTICLE_ID=nullable text, Quantité=nullable number, PU HT=nullable number, Total HT=nullable number, Vente.Date=number, Vente.Client=nullable text, Vente.Référence=nullable text, #"Vente.Date - Copier"=nullable date]}}),
    #"Colonnes renommées" = Table.RenameColumns(#"Lignes groupées",{{"Vente.Client", "Client"}, {"Vente.Référence", "Référence"}, {"Vente.Date", "Année"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"Client", "Référence", "ARTICLE_ID", "Année", "PU HT"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes permutées",{"PU HT.1"}),
    #"Lignes triées" = Table.Sort(#"Colonnes supprimées",{{"Client", Order.Ascending}, {"Référence", Order.Ascending}, {"ARTICLE_ID", Order.Ascending}, {"Année", Order.Ascending}}),
    #"Lignes groupées1" = Table.Group(#"Lignes triées", {"Client", "Référence", "ARTICLE_ID", "Année", "PU HT"}, {{"PU HT.1", each _, type table [Client=nullable text, Référence=nullable text, ARTICLE_ID=nullable text, Année=number, PU HT=nullable number]}}),
    #"Index ajouté" = Table.AddIndexColumn(#"Lignes groupées1", "Index", 1, 1, Int64.Type),
    #"Index ajouté1" = Table.AddIndexColumn(#"Index ajouté", "Index.1", 0, 1, Int64.Type),
    #"Requêtes fusionnées1" = Table.NestedJoin(#"Index ajouté1", {"Index.1", "Client", "Référence", "ARTICLE_ID"}, #"Index ajouté1", {"Index", "Client", "Référence", "ARTICLE_ID"}, "Index ajouté1", JoinKind.LeftOuter),
    #"Index ajouté1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées1", "Index ajouté1", {"PU HT"}, {"PU HT N-1"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Index ajouté1 développé", "Variation", each Number.Round([PU HT] * 100 / [#"PU HT N-1"] - 100, 2), Number.Type)
in
    #"Personnalisée ajoutée"

I'm new at using Power Query so I think I'm using it badly but I don't understand what could be my mistake.

Any idea would be appreciated.

Thank you per advance.

[#2]

Hi @lambrosx 

in the Join statement (line 16 of your PQ statements), you are using also the fields "Client" and "Reference" for the join, which we cannot see in your screenshot.

So maybe there is no match for this field combination for the other articles?

[#3]

Hi @Martin_Weiss, thank you for the answer.

I didn't put those columns for confidentiality but they are well filled.

It's only the column "PU" after joining indexes and the calculated one that are empty after few lines. 

I can't explain why everything is ok in Power Bi et not in excel.