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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.