Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to know if you have another mean (more clean) to get the same result. And if not, how can I modify my code to be more understanble ?
The purpose of the code is to filter the last week of the imported data (from sunday to saturday)
let
Source = Excel.Workbook(File.Contents("C:\Users\TabData.xlsx"), null, true),
Tableau1_Table = Source{[Item="Tableau1",Kind="Table"]}[Data],
AddDateMaxDb = Table.AddColumn(Table.TransformColumnTypes(Tableau1_Table,{{"PLAATY Start
Date", type date}}), "Personnalisé", each
List.Max(Table.TransformColumnTypes(Tableau1_Table,{{"PLAATY Start Date", type
date}})[PLAATY Start Date])),
AddDateL = Table.AddColumn(AddDateMaxDb, "Personnalisé.1", each
Date.AddDays(Date.AddWeeks(Date.StartOfWeek([Personnalisé]),-1),-1)),
#"Personnalisée ajoutée2" = Table.AddColumn(AddDateL, "Personnalisé.2", each if [PLAATY
Start Date] >= Date.AddDays(Date.AddWeeks(Date.StartOfWeek([Personnalisé]),-1),-1)
and [PLAATY Start Date] <=
Date.AddDays(Date.AddWeeks(Date.StartOfWeek([Personnalisé]),-1),+5) then [PLAATY
Start Date] else "O"),
#"Colonnes supprimées" = Table.RemoveColumns(Table.SelectRows(#"Personnalisée ajoutée2",
each ([Personnalisé.2] <> "O")),{"Personnalisé", "Personnalisé.1",
"Personnalisé.2"})
in
#"Colonnes supprimées"
Thanks in advance !
Don
Solved! Go to Solution.
let
Source = Excel.Workbook(File.Contents("C:\Users\TabData.xlsx"), null, true),
Tableau1_Table = Table.TransformColumnTypes(Source{[Item="Tableau1",Kind="Table"]}[Data],{{"PLAATY Start Date", type date}}),
DateMax = List.Max(Tableau1_Table[PLAATY Start Date]),
DateL = Date.AddDays(Date.AddWeeks(Date.StartOfWeek(DateMax),-1),-1),
DateM = Date.AddDays(Date.AddWeeks(Date.StartOfWeek(DateMax),-1),+5),
Custom1 = Table.SelectRows(Tableau1_Table,each [PLAATY Start Date] >=DateL and [PLAATY Start Date] <= DateM))
in
Custom1
let
Source = Excel.Workbook(File.Contents("C:\Users\TabData.xlsx"), null, true),
Tableau1_Table = Table.TransformColumnTypes(Source{[Item="Tableau1",Kind="Table"]}[Data],{{"PLAATY Start Date", type date}}),
DateMax = List.Max(Tableau1_Table[PLAATY Start Date]),
DateL = Date.AddDays(Date.AddWeeks(Date.StartOfWeek(DateMax),-1),-1),
DateM = Date.AddDays(Date.AddWeeks(Date.StartOfWeek(DateMax),-1),+5),
Custom1 = Table.SelectRows(Tableau1_Table,each [PLAATY Start Date] >=DateL and [PLAATY Start Date] <= DateM))
in
Custom1
Wow, thanks a lot, I will learn from that.
Hi @DonPepe ,
Can you provide a copyable example of your source data please?
Make sure to remove any sensitive information before sharing.
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.