Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ItoDiaz
Helper I
Helper I

Select previous month data

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"

 

 

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Hello,

 

If you have the date column with you, you can simple filter out the previous month's data as below:

PC2790_0-1615459871365.png

The corresponding M query code:

= Table.SelectRows(#"Previous Step", each Date.IsInPreviousMonth([Date]))

View solution in original post

1 REPLY 1
PC2790
Community Champion
Community Champion

Hello,

 

If you have the date column with you, you can simple filter out the previous month's data as below:

PC2790_0-1615459871365.png

The corresponding M query code:

= Table.SelectRows(#"Previous Step", each Date.IsInPreviousMonth([Date]))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors