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
dariaglb
Frequent Visitor

Create Index Column based on Dates column in Power Query

Hi!😉

I have table with dates and empty rows.

I need create Index column, based on dates's hierarchy (1...- earlier and to last date)

dariaglb_0-1714375535708.png

Thanks for help in advance!

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @dariaglb, for future requests - provide sample data in usable format please (not as a screenshot). If you don't know how to do it - read note below my post.

 

Result:

dufoq3_0-1714380076970.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/BCcAwCEV38ZzI19gFSgfoPUj336IeAqWpORTEw3s8xN4JUgGhQns9YpOXD4MwlBXaUtsYtrQNHHlYTVsbrWX2H9PBzmvFsHHMc+1tdfpjam38kbbkfgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sbj_num = _t, name_id = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"sbj_num"}, {{"All", each 
        [ a = Table.Sort(Table.AddIndexColumn(_, "IndexHelper", 0, 1),{{"Date", Order.Ascending}}),
          b = Table.AddIndexColumn(Table.SelectRows(a, (x)=> x[Date] <> null), "IndexHelper2", 1, 1),
          c = Table.AddColumn(b, "Index", (x)=> if x[Date] <> null then x[IndexHelper2] else null, Int64.Type),
          d = Table.NestedJoin(a, {"IndexHelper"}, c, {"IndexHelper"}, "H", JoinKind.LeftOuter),
          e = Table.ExpandTableColumn(d, "H", {"Index"}, {"Index"}),
          f = Table.RemoveColumns(Table.Sort(e, {{"IndexHelper", Order.Ascending}}), {"IndexHelper"})
        ][f], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
Kingsketch
Helper I
Helper I

cabn you provide pbix to solve 

dufoq3
Super User
Super User

Hi @dariaglb, for future requests - provide sample data in usable format please (not as a screenshot). If you don't know how to do it - read note below my post.

 

Result:

dufoq3_0-1714380076970.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/BCcAwCEV38ZzI19gFSgfoPUj336IeAqWpORTEw3s8xN4JUgGhQns9YpOXD4MwlBXaUtsYtrQNHHlYTVsbrWX2H9PBzmvFsHHMc+1tdfpjam38kbbkfgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sbj_num = _t, name_id = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"sbj_num"}, {{"All", each 
        [ a = Table.Sort(Table.AddIndexColumn(_, "IndexHelper", 0, 1),{{"Date", Order.Ascending}}),
          b = Table.AddIndexColumn(Table.SelectRows(a, (x)=> x[Date] <> null), "IndexHelper2", 1, 1),
          c = Table.AddColumn(b, "Index", (x)=> if x[Date] <> null then x[IndexHelper2] else null, Int64.Type),
          d = Table.NestedJoin(a, {"IndexHelper"}, c, {"IndexHelper"}, "H", JoinKind.LeftOuter),
          e = Table.ExpandTableColumn(d, "H", {"Index"}, {"Index"}),
          f = Table.RemoveColumns(Table.Sort(e, {{"IndexHelper", Order.Ascending}}), {"IndexHelper"})
        ][f], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thanks a ton! It's working.🔥

I am very thankful for your kind help again)

and I appreciate your advice.

AlienSx
Super User
Super User

let
    source = table_with_dates_and_empty_rows,
    sort_index = Table.AddIndexColumn(source, "sort_index"),
    fx = (tbl) => 
        if tbl[Date]{0} = null 
            then tbl 
            else Table.AddIndexColumn(
                Table.Sort(tbl, "Date"), 
                "Index", 
                1, 1
            ),
    gr = Table.Group(
        sort_index, 
        "Date", 
        {"x", fx}, 
        GroupKind.Local, 
        (s, c) => Number.From(List.NonNullCount({s, c}) = 1 )
    )[x],
    combine = Table.Combine(gr),
    sort_back = Table.Sort(combine, "sort_index")[[sbj_num], [name_id], [Date], [Index]]
in
    sort_back

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