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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marsclone
Helper IV
Helper IV

Dynamic Index

Hi,

I would like to add an index number with a measure.

When column FM+ (=Measure) has an value then start index with 1.

 

How can i achieve this?

Thank you in advance

 

 

marsclone_2-1674824883895.png

 

 

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

The code below will start the Index column at the first Non-Null in the FM+ column.

 

Read the code comments as the code can be simplified if nulls will ONLY be at the beginning of the column and not interspersed within the column.

 

Since it does not depend on a Join (merge), there is no issue with duplicates.

 

let
   Source = Table.FromColumns(
       {{1..9},
        {null,null, 2,5,9,14,null,27,35}},
        type table[FM=Int64.Type, #"FM+"=Int64.Type]),

//Add index starting at first non-blank entry in FM+

//use next line if there will never be nulls except at the beginning of the column
//    #"FM+ 1st" = Table.RowCount(Source) - List.NonNullCount(Source[#"FM+"]),

//if nulls could be anywhere but we need to start at the First non-null then
    #"FM+ 1st" = 
        List.Max(
            List.Accumulate(
                List.PositionOf(Source[#"FM+"],null,Occurrence.All),
                {}, (state, current)=>
                if state = {} and current=0 then {0}
                    else if List.Last(state) + 1 = current then state & {current}
                    else state & {null}
        ))+1,

    #"Index List" = List.Repeat({null}, #"FM+ 1st") & {1..Table.RowCount(Source) - #"FM+ 1st"},
    #"Add Index" =  Table.FromColumns(
        Table.ToColumns(Source) & {#"Index List"},
        type table[FM=Int64.Type, #"FM+"=Int64.Type, Index=Int64.Type]
    )
in 
    #"Add Index"

 

Example

ronrsnfld_0-1675044528765.png

 

 

 

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

The code below will start the Index column at the first Non-Null in the FM+ column.

 

Read the code comments as the code can be simplified if nulls will ONLY be at the beginning of the column and not interspersed within the column.

 

Since it does not depend on a Join (merge), there is no issue with duplicates.

 

let
   Source = Table.FromColumns(
       {{1..9},
        {null,null, 2,5,9,14,null,27,35}},
        type table[FM=Int64.Type, #"FM+"=Int64.Type]),

//Add index starting at first non-blank entry in FM+

//use next line if there will never be nulls except at the beginning of the column
//    #"FM+ 1st" = Table.RowCount(Source) - List.NonNullCount(Source[#"FM+"]),

//if nulls could be anywhere but we need to start at the First non-null then
    #"FM+ 1st" = 
        List.Max(
            List.Accumulate(
                List.PositionOf(Source[#"FM+"],null,Occurrence.All),
                {}, (state, current)=>
                if state = {} and current=0 then {0}
                    else if List.Last(state) + 1 = current then state & {current}
                    else state & {null}
        ))+1,

    #"Index List" = List.Repeat({null}, #"FM+ 1st") & {1..Table.RowCount(Source) - #"FM+ 1st"},
    #"Add Index" =  Table.FromColumns(
        Table.ToColumns(Source) & {#"Index List"},
        type table[FM=Int64.Type, #"FM+"=Int64.Type, Index=Int64.Type]
    )
in 
    #"Add Index"

 

Example

ronrsnfld_0-1675044528765.png

 

 

 

@ronrsnfld 

Thank you for your help.

The problem that i have is , that FM and FM+ are measures. I have these in Power Pivot, so not visible in Power Query.

I didn't thought it was so hard, to add a subsequent number?

 

I have added the file: Testfile 

 

So FM is a sum of some values. When "Opmerking" has an "x" then FM+ is null, when not then take value and in the next row plus previous value. When FM+ has an value then start a simple index for the following rows.

 

I hope someone can help with this?

 

desired outcome:

marsclone_0-1675103631455.png

 

 

alannavarro
Resolver I
Resolver I

this should work but is using a merge, it will only work if the column FM+ has not duplicate values.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBFQAQDATRXvbsQIRQS57+27DW7R9mMtFQgFMSRpjUqSE5taVBNRfnC6sYjyEusv9rk872XA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FM = _t, #"FM+" = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([#"FM+"] <> null and [#"FM+"] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
Custom1 = Table.NestedJoin(Source,"FM+",#"Added Index","FM+","Custom"),
#"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Index"}, {"Index"})
in
#"Expanded Custom"

adudani
Super User
Super User

hi @marsclone,

 

Think I was  able to resolve it in power query.

 

Create 4 blank queries and copy paste the below intot the advanced editor:

 

Example1 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBFQAQDATRXvbsQIRQS57+27DW7R9mMtFQgFMSRpjUqSE5taVBNRfnC6sYjyEusv9rk872XA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FM = _t, #"FM+" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FM", Int64.Type}, {"FM+", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"FM+"] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Example 1 output

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBFQAQDATRXvbsQIRQS57+27DW7R9mMtFQgFMSRpjUqSE5taVBNRfnC6sYjyEusv9rk872XA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FM = _t, #"FM+" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FM", Int64.Type}, {"FM+", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"FM+"}, #"Example1 (2)", {"FM+"}, "Example1 (2)", JoinKind.LeftOuter),
    #"Expanded Example1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Example1 (2)", {"Index"}, {"Example1 (2).Index"})
in
    #"Expanded Example1 (2)"

Example2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncu5EQAgDAPBXhSTYPOYWjz03wZCDNkGd5moKMAuCftwwqVGTalT1cRxGeIkrYtxp3ctsrHdBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FM = _t, #"FM+" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FM", Int64.Type}, {"FM+", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"FM+"] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

 

Example 2 output

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncu5EQAgDAPBXhSTYPOYWjz03wZCDNkGd5moKMAuCftwwqVGTalT1cRxGeIkrYtxp3ctsrHdBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FM = _t, #"FM+" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FM", Int64.Type}, {"FM+", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"FM+"}, Example2, {"FM+"}, "Example2", JoinKind.LeftOuter),
    #"Expanded Example2" = Table.ExpandTableColumn(#"Merged Queries", "Example2", {"Index"}, {"Example2.Index"})
in
    #"Expanded Example2"

 

Steps taken:

1. duplicated the initial query.

2. Filtered the FM+ column removing nulls.

3. Added index column from 1.

4. left merge with initial query and expand merge index.

 

 

 

 

Appreciate a thumbs up if this is helpful.

 

please let me know if this resovles the question.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors