Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!