how to trace the changes of name

[tangutoori]

Hi All,

i got with a scenario.

I have a table with two columns prev num and current number. At any instance of date prev number can to current number.

INPUT

S.no	Prev Num	Curr Num	Date
1	30y0134	30Y0138	30-02-2023
2	30y0145	30Y0167	03-03-23
3	30Y0138	30Y0148	20-03-23
4	30Y0148	30Y0150	30-03-23

If we select 30Y0134, we need to show all the trasactions related to it, after that date. i mean records  1,3,4 should show up. As they are indirectly linked.

Out Put for 30Y0134

S.no	Prev Num	Curr Num	Date
1	30y0134	30Y0138	30-02-2023
3	30Y0138	30Y0148	20-03-23
4	30Y0148	30Y0150	30-03-23

If we select 30Y0145, we need to show all the trasactions related to it,after that date. i mean records  3,4 should show up. As they are indirectly linked.

OUTPUT for 30Y0138

S.no	Prev Num	Curr Num	Date
3	30Y0138	30Y0148	20-03-23
4	30Y0148	30Y0150	30-03-23

Regards,

Narender.

[Adamboer]

To achieve this in Power Query Editor, you can follow the steps below:

1. Load the input table into Power Query Editor.

2. Create a parameter for "Prev Num" that will allow the user to select a specific value.

3. Filter the table to show only rows where the "Prev Num" matches the parameter value.

4. Add a custom column that uses the "Prev Num" from the previous row as a lookup value to identify indirect links.

5. Expand the table to show only the relevant columns.

6. Filter the table to show only rows where the date is after the selected "Prev Num".

Here are the specific steps:

1. Load the input table into Power Query Editor by selecting "From Table" in the "Home" tab.

2. Create a parameter by selecting "New Parameter" in the "View" tab. Set the name to "Prev Num" and the type to "Text". Click "OK" to create the parameter.

3. Filter the table to show only rows where the "Prev Num" matches the parameter value. To do this, click on the drop-down arrow in the "Prev Num" column header, select "Text Filters", then "Equals". In the dialog box, select the "Prev Num" parameter and click "OK".

4. Add a custom column by selecting "Add Column" in the "Add Column" tab. In the "Custom Column" dialog box, enter the following formula:

   = Table.Column(Table.SelectRows(#"Filtered Rows", each [Prev Num] = [#"Prev Num"])[Curr Num], List.Max({0..List.PositionOf(Table.SelectRows(#"Filtered Rows", each [Prev Num] = [#"Prev Num"])[Prev Num], [Prev Num]))})

   This formula looks up the "Curr Num" value from the row where the "Prev Num" matches the "Prev Num" value of the current row, but with the highest "Prev Num" value that is less than the current row's "Prev Num". This will identify any indirect links between rows.

5. Expand the table to show only the relevant columns. To do this, select the drop-down arrow in the "Custom" column header, then select "Expand" and select only the "Prev Num", "Curr Num", and "Date" columns.

6. Filter the table to show only rows where the date is after the selected "Prev Num". To do this, click on the drop-down arrow in the "Date" column header, select "Date Filters", then "After". In the dialog box, select the "Prev Num" parameter and click "OK".

Now, when you select a value for the "Prev Num" parameter, the output table will show all rows that are indirectly linked to that value and have a date after it.

[tangutoori]

by using Anti join i have acheived this. 

[lbendlin]

30-02-2023

the what now?

Is it possible for a current number to point to more than one previous number?  If not then you can use the PATH functions.