Forum Discussion
Leon_Redclifft
Feb 06, 2020Copper Contributor
Need help with Power Query
In the picture, i have one row called Issued_Date which is followed in sequence date. How do I make a step that allow the date to be Not followed by sequence? Please help! Thanks!
- Feb 07, 2020
If you mean something like this
you may add index column, after that custom column where to check if previous date is equal to current one then return null else date. Plus cosmetic.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangeType = Table.TransformColumnTypes( Source, {{"A", type text}, {"Date", type date}} ), AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1), AddCustom = Table.AddColumn( AddIndex, "Custom", each if ([Index] > 0) and (AddIndex[Date]{[Index]-1} = AddIndex[Date]{[Index]}) then null else [Date]), RemoveOtherColumns = Table.SelectColumns(AddCustom,{"A", "Custom"}), RenameColumns = Table.RenameColumns(RemoveOtherColumns,{{"Custom", "Date"}}) in RenameColumns
PascalKTeam
Feb 06, 2020Iron Contributor
Can you post a picture please
- Leon_RedclifftFeb 06, 2020Copper Contributor
- SergeiBaklanFeb 07, 2020Diamond Contributor
If you mean something like this
you may add index column, after that custom column where to check if previous date is equal to current one then return null else date. Plus cosmetic.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangeType = Table.TransformColumnTypes( Source, {{"A", type text}, {"Date", type date}} ), AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1), AddCustom = Table.AddColumn( AddIndex, "Custom", each if ([Index] > 0) and (AddIndex[Date]{[Index]-1} = AddIndex[Date]{[Index]}) then null else [Date]), RemoveOtherColumns = Table.SelectColumns(AddCustom,{"A", "Custom"}), RenameColumns = Table.RenameColumns(RemoveOtherColumns,{{"Custom", "Date"}}) in RenameColumns - PascalKTeamFeb 07, 2020Iron ContributorPlease also post a picture of what the solution should look like, thanks