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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Syrathos
New Member

Index (Or Equivalent) that skips existing values (different row).

I have spent hours googling and trying various things to no avail so I thought I'd check in with the helpful individuals here.

 

I have a table in Excel which I am manipulating using power query:

 

TypeRelated IDDateCodeQTYCostIndexDesired
Purchase 10/01/2023ABC1

100

11
Purchase 15/02/2023ABC110023
Purchase 17/02/2023ABC110034
Purchase210/03/2023ABC110042
Purchase 05/01/2023DEF17511
Purchase 02/02/2023DEF17522

 

I am after a way of having an 'index' (or equivalent) that skips the value that exists in column Related ID (but where that index would normally be). Looking at the "Desired" column, row 2 would normally have the index of 2, but since 2 exists in "Related ID" (albeit on row 4) it skips to 3. 2 then shows on the row that has the "Related ID" (row 4). The "Related ID" could be any number and some products may have multiple rows with "Related ID".

1 ACCEPTED SOLUTION

@Syrathos 

let
    Source = your_table,
    group = Table.Group(Source, {"Code"}, {{"all", each fx_tbl(_)}}),
    fx_tbl = (tbl as table) as table =>
        [a = Table.AddColumn(tbl, "sorting", each if List.Contains({null, "", " "}, [Related ID]) then [Index] else [Related ID] - .1),
        b = Table.Sort(a, "sorting"),
        idx = Table.AddIndexColumn(b, "Desired", 1, 1)][idx],
    z = Table.RemoveColumns(Table.Combine(group[all]), "sorting")
in
    z

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

@Syrathos , first, group your rows by "Code". For each group add new column for sorting like this: if [Related ID] = null then [Index] else [Related ID] - 0.1. Then sort your groups by this new column and add new index column. 

Apologies for the delay, I don't know where time has gone. I'm still relatively new to playing around with power query, but it I group the rows by "Code" I seem to lose access to the other column details when trying to add a new column with the if then else statement.

@Syrathos 

let
    Source = your_table,
    group = Table.Group(Source, {"Code"}, {{"all", each fx_tbl(_)}}),
    fx_tbl = (tbl as table) as table =>
        [a = Table.AddColumn(tbl, "sorting", each if List.Contains({null, "", " "}, [Related ID]) then [Index] else [Related ID] - .1),
        b = Table.Sort(a, "sorting"),
        idx = Table.AddIndexColumn(b, "Desired", 1, 1)][idx],
    z = Table.RemoveColumns(Table.Combine(group[all]), "sorting")
in
    z

Thanks so much! That appears to have done the trick!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors