Dear All,
In this code, I need to select data of my previous month, it must take into account the case when my previous month is december.
I have tried using a selection of data of my Previous and my current year :
#"Selectionannées" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([ANNEE] = Currentyear or [ANNEE] = Previousyear)),
It "works" if my previous month is december, but for the reste of the months it generates conflicts and duplications when I apply my step :
#"AjoutCleTmp" = Table.AddColumn(#"MoisPrecedent","CleTmp", each Number.ToText(Currentmonth) & "-" & Number.ToText(Currentyear) & "-" & [Attribute]),
As I have data of my previous month for both previous and current year!!
I have tried to select my previous month using this code :
//#"MoisPrecedent" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each if
//Currentmonth=1
//then
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth and [ANNEE] = Previousyear))
//else
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth))),
But query M send the message saying it's not possible to convert a table type in fonction type... and vice versa.
How could I select only previous month data? I'm not a query M expert, I would appreciate step by step explanations
Thanks for your help!
let
Source = FL_ET3_KPI,
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Currentmonth=Date.Month(TodaysDate),
Previousmonth=Date.Month(Date.AddMonths(TodaysDate,-1)),
Currentyear=Date.Year(TodaysDate),
Previousyear=Date.Year(Date.AddYears(TodaysDate,-1)),
#"Autres colonnes supprimées" = Table.SelectColumns(FL_ET3_KPI,{"CHRONO", "SITE", "LIGNE", "DATE", "PRODUIT", "VALIDITE_BOBINE", "MOIS", "SEMAINE", "CONFORMITE BOBINE_PMPP", "CONFORMITE BOBINE_PCP", "CONFORMITE PCP_MOYENNE", "CONFORMITE PP_MOYENNE", "Concentration DG_capteur", "Temperature DG", "Debit Circulation Brosses", "Debit Rampes Brosses", "Conc DKP_Capteur", "Temp DKP", "Deconcentr DKP", "Conc Fer DKP", "Debit Rincage 1 DKP_cascade", "Debit Rincage 2 DKP", "Pression Gr 1", "Stanneux Etamage AN1", "Acidite Etamage AN1", "Debit Circulation Electrolyte", "Volumes Etamage", "Acidite Rincage 1 Etamage", "Stanneux Rincage 1 Etamage", "Acidite Rincage 2 _ AN1", "Stanneux Rincage 2 _ AN1", "Pression Groupe 3", "Pression Groupe 4", "Debit Air Chaud Secheur 1", "Temperature Air Chaud Secheur 1", "Debit Rampe SUP _ BdT", "Debit Rampe INF _ BdT", "Temperature BdT", "Concentration Bichromate TC_Capteur", "pH _ TC_Capteur", "Temperature TC", "Debit Rincage 2 TC", "Chromates Rincages 2 TC", "Pression Groupe 5", "Debit Laveur Vapeur", "Pression Groupe 6", "Pression Groupe 7", "Debit Air Chaud Secheur 3", "Temperature Air Chaud Secheur 3", "Tension Pre_ioniseur SUP", "Intensite Pre_Ioniseur SUP", "Tension Pre_ioniseur INF", "Intensite Pre_ioniseur INF", "Tension Planques Repulsives", "Intensite Plaques Repulsives"}),
PreviousStep = Table.TransformColumnTypes(#"Autres colonnes supprimées",{{"CHRONO", Int64.Type}, {"SITE", type text}, {"LIGNE", type text}, {"DATE", type date}, {"PRODUIT", type text}, {"VALIDITE_BOBINE", type any}, {"MOIS", Int64.Type}, {"SEMAINE", Int64.Type}, {"CONFORMITE BOBINE_PMPP", Int64.Type}, {"CONFORMITE BOBINE_PCP", Int64.Type}, {"CONFORMITE PCP_MOYENNE", type number}, {"CONFORMITE PP_MOYENNE", type number}, {"Concentration DG_capteur", Int64.Type}, {"Temperature DG", Int64.Type}, {"Debit Circulation Brosses", Int64.Type}, {"Debit Rampes Brosses", Int64.Type}, {"Conc DKP_Capteur", Int64.Type}, {"Temp DKP", Int64.Type}, {"Deconcentr DKP", Int64.Type}, {"Conc Fer DKP", Int64.Type}, {"Debit Rincage 1 DKP_cascade", Int64.Type}, {"Debit Rincage 2 DKP", Int64.Type}, {"Pression Gr 1", Int64.Type}, {"Stanneux Etamage AN1", Int64.Type}, {"Acidite Etamage AN1", Int64.Type}, {"Debit Circulation Electrolyte", Int64.Type}, {"Volumes Etamage", Int64.Type}, {"Acidite Rincage 1 Etamage", Int64.Type}, {"Stanneux Rincage 1 Etamage", Int64.Type}, {"Acidite Rincage 2 _ AN1", Int64.Type}, {"Stanneux Rincage 2 _ AN1", Int64.Type}, {"Pression Groupe 3", Int64.Type}, {"Pression Groupe 4", Int64.Type}, {"Debit Air Chaud Secheur 1", Int64.Type}, {"Temperature Air Chaud Secheur 1", Int64.Type}, {"Debit Rampe SUP _ BdT", Int64.Type}, {"Debit Rampe INF _ BdT", Int64.Type}, {"Temperature BdT", Int64.Type}, {"Concentration Bichromate TC_Capteur", Int64.Type}, {"pH _ TC_Capteur", Int64.Type}, {"Temperature TC", Int64.Type}, {"Debit Rincage 2 TC", Int64.Type}, {"Chromates Rincages 2 TC", Int64.Type}, {"Pression Groupe 5", Int64.Type}, {"Debit Laveur Vapeur", Int64.Type}, {"Pression Groupe 6", Int64.Type}, {"Pression Groupe 7", Int64.Type}, {"Debit Air Chaud Secheur 3", Int64.Type}, {"Temperature Air Chaud Secheur 3", Int64.Type}, {"Tension Pre_ioniseur SUP", Int64.Type}, {"Intensite Pre_Ioniseur SUP", Int64.Type}, {"Tension Pre_ioniseur INF", Int64.Type}, {"Intensite Pre_ioniseur INF", Int64.Type}, {"Tension Planques Repulsives", Int64.Type}, {"Intensite Plaques Repulsives", Int64.Type}}),
#"Personnalisée ajoutée" = Table.AddColumn(PreviousStep, "ANNEE", each Date.Year([DATE])),
#"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([MOIS] <> null)),
DateList = List.Transform(List.Distinct(#"Lignes filtrées"[MOIS]), each {Text.From(_), Percentage.Type}),
ListLight = Table.RemoveColumns(#"Lignes filtrées",{"SITE", "LIGNE", "DATE", "PRODUIT", "VALIDITE_BOBINE", "SEMAINE", "CONFORMITE BOBINE_PMPP", "CONFORMITE BOBINE_PCP", "CONFORMITE PCP_MOYENNE", "CONFORMITE PP_MOYENNE"}),
Columns = List.Select(Table.ColumnNames(ListLight), each _ <> "MOIS" and _ <> "CHRONO" and _ <> "ANNEE"),
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each (List.Sum(["& _ & "]) / Table.RowCount(_))", [List.Sum = List.Sum, Table.RowCount = Table.RowCount]), Number.Type}),
#"Grouped Rows" = Table.Group(#"Lignes filtrées", {"MOIS" , "ANNEE"}, GroupList),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"MOIS" , "ANNEE"}, "Attribute", "TauxMois"),
#"Personnalisée ajoutée4" = Table.AddColumn(#"Unpivoted Other Columns", "Cle", each Number.ToText([MOIS]) & "-" & Number.ToText([ANNEE]) & "-" & [Attribute]),
#"Add Key1" = Table.ReplaceKeys(#"Personnalisée ajoutée4",{[Columns={"MOIS"},Primary = false]}),
#"Add Key2" = Table.AddKey(#"Add Key1", {"Cle"}, true),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Add Key2", "Alarme", each if [TauxMois] <= 0.8 then true else false),
#"Selectionannées" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([ANNEE] = Currentyear or [ANNEE] = Previousyear)),
#"MoisCourant" = Table.SelectRows(#"Selectionannées", each ([MOIS] = Currentmonth)),
//#"MoisPrecedent" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each if
//Currentmonth=1
//then
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth and [ANNEE] = Previousyear))
//else
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth))),
#"MoisPrecedent" = Table.SelectRows(#"Selectionannées", each ([MOIS] = Previousmonth)),
#"AjoutCleTmp" = Table.AddColumn(#"MoisPrecedent","CleTmp", each Number.ToText(Currentmonth) & "-" & Number.ToText(Currentyear) & "-" & [Attribute]),
#"MoisCourantUpdate" = Table.NestedJoin(#"MoisCourant", {"Cle"}, #"AjoutCleTmp", {"CleTmp"}, "MoisN-1", JoinKind.LeftOuter),
#"MoisN-1 développé" = Table.ExpandTableColumn(MoisCourantUpdate, "MoisN-1", {"Alarme"}, {"MoisN-1.Alarme"}),
#"Colonne conditionnelle ajoutée1" = Table.AddColumn(#"MoisN-1 développé", "AlarmeRedPCP", each if [Alarme] = true then true else if [#"MoisN-1.Alarme"] = true then true else false),
#"Lignes filtrées1" = Table.SelectRows(#"Colonne conditionnelle ajoutée1", each ([MOIS] = Currentmonth and [ANNEE] = Currentyear)),
#"Doublons supprimés" = Table.Distinct(#"Lignes filtrées1", {"Cle"})
in
#"Doublons supprimés"
