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
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
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
Top Kudoed Authors