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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pmendoza
Frequent Visitor

Filtering records in Advanced Editor

Need to know what filter to place in Advanced Editor so that I can select only records that have a date > 01-01-2014 to be imported into BI.

 

3 REPLIES 3
ankitpatira
Community Champion
Community Champion

@pmendoza Just ignore my previous comment. Below is the code (to be used in Advanced Editor) that loads data for one table and filters date column where date is greater than 01-01-2014  if that is what you're after.

 

let
Source = Sql.Database("SERVERNAME", "DATABASE"),
dbo_ConditionTimeline = Source{[Schema="dbo",Item="TABLENAME"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_ConditionTimeline, each [COLUMNNAME] > #date(2014, 1, 1))
in
#"Filtered Rows"

 

 

Hi, Everyone.

 

Need your help to check how to put a Table.SelectRows using this column [Business GroupbyBL1.0] with this distinct value "Manila IT" in this formula. Whenever I try to input it, it errors.

 

let
CurrentTemplate = let
Source = Excel.Workbook(File.Contents("Y:\Dashboard).xlsx"), null, true),
CurrentTemplate_Sheet = Source{[Item="CurrentTemplate",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(CurrentTemplate_Sheet),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"EmployeeNumber", Int64.Type}, {"LegacyNumber", type text}, {"TINNumber", type text}, {"PhilHealthNumber", type text}, {"PAG-IBIGNumber", type text}, {"ActiveDirectory", type text}, {"PersonType", type text}, {"SSSNumber", Int64.Type}, {"FullName", type text}, {"Prefix", type text}, {"Title", type text}, {"FirstName", type text}, {"MiddleName", type text}, {"LastName", type text}, {"Suffix", type text}, {"PreferredName", type text}, {"Gender", type text}, {"BirthDate", type date}, {"TownOfBirth", type text}, {"RegionOfBirth", type text}, {"CountryOfBirth", type text}, {"MaritalStatus", type text}, {"Nationality", type text}, {"RegisteredDisabled", type text}, {"Office", type text}, {"Location", type text}, {"Mailstop", type text}, {"Email", type text}, {"MailTo", type text}, {"CorrespondenceLanguage", type text}, {"DateFirstHired", type date}, {"AdjustedServiceDate", type any}, {"LatestStartDate", type date}, {"ActualTerminationDate", type any}, {"FinalProcessDate", type any}, {"AddressDateFrom", type any}, {"AddressDateTo", type text}, {"AddressLine1", type text}, {"AddressLine2", type text}, {"Barangay/District", type text}, {"City/Municipality", type text}, {"Province", type text}, {"Country", type text}, {"ZipCode", type text}, {"HomePhone", type text}, {"WorkPhone", type text}, {"Mobile", type text}, {"Mobile-Personal", type text}, {"Organization", type text}, {"JobGroup", type text}, {"Job", type text}, {"PositionTitle", type text}, {"PayrollName", type text}, {"LocationName", type text}, {"Status", type text}, {"Vacancy", type text}, {"AssignmentNumber", type text}, {"AssignmentCategory", type text}, {"EmployeeCategory", type text}, {"SalaryBasis", type text}, {"GradeName", type text}, {"SupervisorName", type text}, {"SupervisorNumber", Int64.Type}, {"SupervisorBusinessGroup", type text}, {"ProbationPeriodLength", type text}, {"ProbationPeriodUnits", type text}, {"ProbationEndDate", type text}, {"NoticePeriodLength", type text}, {"NoticePeriodUnits", type text}, {"WorkingHours", type any}, {"Frequency", type text}, {"Hourly/SalariedBasis", type text}, {"NormalStartTime", type text}, {"NormalEndTime", type text}, {"WorkingAtHome", type text}, {"CompanyDetails", type text}, {"Reason", type text}, {"TaxStatus", type text}, {"PayCode", Int64.Type}, {"CostCenter", Int64.Type}, {"OvertimeAllowed?", type text}, {"OvertimeApprover", type text}, {"TransportationAllowanceEligible", type text}, {"ExpenseApprover", type text}, {"DepartmentManager", type text}, {"NSTSZone", type text}, {"OtherHireDate(SYKES)", type text}, {"USParentOrganization", type text}, {"JobClassification", type text}, {"EmersonBusiness", type text}, {"CostingDateFrom", type any}, {"CostingDateTo", type text}, {"BusinessUnit", type text}, {"Location_1", type text}, {"Department", type text}, {"ProductGroup", type text}, {"Intercompany", type text}, {"EmploymentStatus", type text}, {"JobCombiTitle", type text}, {"JobCombiFunction", type text}, {"BusinessGroupsbyBL2.0", type text}, {"Business GroupbyBL1.0", type text}, {"TenureinDays", type number}, {"TenureinYears", type number}, {"TenureinMonths", type number}, {"DescriptiveTenure", type text}, {"Age", type number}, {"Generation", type text}, {"JobStream", type text}, {"JobLevel", type text}, {"JobGrade", type text}, {"MonthHiredTenure", type date}, {"RegularizationMonth", type text}, {"ReasonforVacancy", type text}, {"ReportingMonthYear", type date}, {"Capability", type text}, {"ManagementRole", type text}, {"Company Being Supported", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "BirthDate", "BirthDate - Copy"),
#"Extracted Year" = Table.TransformColumns(#"Duplicated Column",{{"BirthDate - Copy", Date.Year}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"BirthDate - Copy", "Birth Year"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Generation Landscape 2.0", each if [Birth Year] >= 1981 then "Millennial" else if [Birth Year] <= 1960 then "Baby Boomer" else "Generation X" ),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Civil Status", each if [TaxStatus] = "S" then "Single" else if Text.Contains([TaxStatus], "M") then "Married" else "Single Parent" ),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column1", "Custom", each DateTime.LocalNow()),
#"Extracted Month" = Table.TransformColumns(#"Added Custom",{{"Custom", Date.Month}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Month",{{"Custom", "Current Month"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "MonthHiredTenure", "MonthHiredTenure - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"MonthHiredTenure - Copy", "Month Hired - Flag"}}),
#"Extracted Month1" = Table.TransformColumns(#"Renamed Columns2",{{"Month Hired - Flag", Date.Month}}),
#"Added Conditional Column2" = Table.AddColumn(#"Extracted Month1", "New Hire Flag", each if [#"Month Hired - Flag"] = [Current Month] then "New Hire" else "Previous Hire" ),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Current Month", "Month Hired - Flag"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each DateTime.LocalNow()),
#"Inserted Date" = Table.AddColumn(#"Added Custom1", "Date", each DateTime.Date([Custom]), type date),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Date",{"Date"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Current Date"}}),
#"Extracted Date" = Table.TransformColumns(#"Renamed Columns3",{{"Current Date", DateTime.Date}}),
#"Added Custom2" = Table.AddColumn(#"Extracted Date", "Custom", each Date.Year([Current Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each Date.ToText([Current Date], "yyyy/MM")),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Custom"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "Current M+Y"}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns4", "MonthHiredTenure", "MonthHiredTenure - Copy"),
#"Removed Columns3" = Table.RemoveColumns(#"Duplicated Column2",{"MonthHiredTenure - Copy"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Month Hired M+Y", each Date.ToText([MonthHiredTenure],"yyyy/MM")),
#"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"New Hire Flag"}),
#"New Hire Formula" = Table.AddColumn(#"Removed Columns4", "New Hire Flag", each if [#"Month Hired M+Y"] = [#"Current M+Y"] then "1" else "0" )
in
#"New Hire Formula"
in
CurrentTemplate

 

 

ankitpatira
Community Champion
Community Champion

@pmendoza Which Advanced Editor do you mean ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors