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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tangutoori
Helper III
Helper III

how to trace the changes of name

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.noPrev NumCurr NumDate
130y013430Y013830-02-2023
230y014530Y016703-03-23
330Y013830Y014820-03-23
430Y014830Y015030-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.noPrev NumCurr NumDate
130y013430Y013830-02-2023
330Y013830Y014820-03-23
430Y014830Y015030-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.noPrev NumCurr NumDate
330Y013830Y014820-03-23
430Y014830Y015030-03-23

 

Regards,

Narender.

 

 

3 REPLIES 3
Adamboer
Responsive Resident
Responsive Resident

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
Helper III
Helper III

by using Anti join i have acheived this. 

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors