Forum Discussion

itsme's avatar
itsme
Icon for Resolver I rankResolver I
4 years ago
Solved

Create Index that increases every time a value in different column changes

I need to create an index column that increases +1 every time the flag value changes back and forth. It needs to be in ascending order by Timestamp as well (which is aligned with Index). I created a basic index from 0. I also tried creating a grouped one (Counter) in PQ but that didn't work as expected. The column 'Desired Result' is what I need. Can anyone please help me? Whether it's in PQ or DAX.

 

TimestampIndexFlagCounterDesired Result
8/20/2016 4:320Bad11
8/20/2016 4:381Bad21
8/20/2016 4:432Bad31
8/20/2016 4:483Bad41
8/20/2016 4:534Bad51
8/20/2016 4:585Bad61
8/20/2016 5:036Bad71
8/20/2016 5:087Bad81
8/20/2016 5:138Bad91
8/20/2016 5:199Good12
8/20/2016 5:2410Good22
8/20/2016 5:2911Good32
8/20/2016 5:3412Good42
8/20/2016 5:3913Bad103
8/20/2016 5:4414Bad113
8/20/2016 5:4915Bad123
8/20/2016 5:5416Good54
8/20/2016 5:5917Bad135
8/20/2016 6:0418Bad145
8/20/2016 6:0919Good66
8/20/2016 6:1420Good76
8/20/2016 6:2021Good86
8/20/2016 6:2522Bad157
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    itsme  can be done with PQ for sure

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdIxDoMwDAXQq1SZKzWxk2AYu/QQiKFSd+6/lX67MiKWCMt/OInNuiZ5UD6e0m91YUr3lI/1fH+Od8Ha7hckFimiEFW2SBHHSCxSVEPU2CJFLUZikaIeoLZktkjRFCOxSJGEqLBFiuYYzRa99v3fTRoU4dbZGcUMexRnHDJGNXJWY4Zq3nnszwOrqOa9x/4BQzXvPvYfWUO17mdrON/AUM0ngGO2C+tLRjWfAY4ZMFQ7TaFjXRm+ptMUppBB0GkKErPfzcj/fvRmStv2BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Index = _t, Flag = _t, Counter = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Index", Int64.Type}, {"Flag", type text}, {"Counter", Int64.Type}, {"Desired Result", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Counter", "Desired Result", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Flag"}, {"Flag.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Index] = 1 then 1 else if [Flag] <> [Flag.1] then 1 else 0),
    Custom = #"Added Custom"[Custom],
    Custom1 = List.Generate(()=>[i=0,j= Custom{0},k=j],
                            each [i]<List.Count( Custom),
                            each [i=[i]+1,j=[j]+ Custom{i},k=j], 
                            each [k]),
    Combine = List.Combine({Table.ColumnNames(#"Added Custom"),{"desiredIndex"}}),
    new = Table.FromColumns(Table.ToColumns(#"Added Custom")&{Custom1},Combine),
    #"Removed Columns1" = Table.RemoveColumns(new,{"Index.1", "Flag.1", "Custom"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Index", Order.Ascending}})                   
in
    #"Sorted Rows1"

     

     

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    itsme start a new power query, click on advanced editor and paste this code and you can see the steps and apply on your table:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdIxDoMwDAXQq1SZKzWxk2AYu/QQiKFSd+6/lX67MiKWCMt/OInNuiZ5UD6e0m91YUr3lI/1fH+Od8Ha7hckFimiEFW2SBHHSCxSVEPU2CJFLUZikaIeoLZktkjRFCOxSJGEqLBFiuYYzRa99v3fTRoU4dbZGcUMexRnHDJGNXJWY4Zq3nnszwOrqOa9x/4BQzXvPvYfWUO17mdrON/AUM0ngGO2C+tLRjWfAY4ZMFQ7TaFjXRm+ptMUppBB0GkKErPfzcj/fvRmStv2BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Index = _t, Flag = _t, Counter = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Index", Int64.Type}, {"Flag", type text}, {"Counter", Int64.Type}, {"Desired Result", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Desired Result", "Counter"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index", {"Index.1"}, "Added Index", JoinKind.LeftOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Flag"}, {"Flag.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index",{{"Index.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Match", each if [Flag]<>[Flag.1] then [Index] else null, Int64.Type),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Match"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index", "Index.1", "Flag.1"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Flag", "Match"}, {{"All", each _, type table [Timestamp=nullable datetime, Flag=nullable text, Match=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 1, 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index1", "All", {"Timestamp"}, {"Timestamp"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded All",{"Match"})
in
    #"Removed Columns2"

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • itsme's avatar
      itsme
      Icon for Resolver I rankResolver I
      4 years ago

      Haven't tried this solution but thank you.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    itsme  can be done with PQ for sure

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdIxDoMwDAXQq1SZKzWxk2AYu/QQiKFSd+6/lX67MiKWCMt/OInNuiZ5UD6e0m91YUr3lI/1fH+Od8Ha7hckFimiEFW2SBHHSCxSVEPU2CJFLUZikaIeoLZktkjRFCOxSJGEqLBFiuYYzRa99v3fTRoU4dbZGcUMexRnHDJGNXJWY4Zq3nnszwOrqOa9x/4BQzXvPvYfWUO17mdrON/AUM0ngGO2C+tLRjWfAY4ZMFQ7TaFjXRm+ptMUppBB0GkKErPfzcj/fvRmStv2BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Index = _t, Flag = _t, Counter = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Index", Int64.Type}, {"Flag", type text}, {"Counter", Int64.Type}, {"Desired Result", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Counter", "Desired Result", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Flag"}, {"Flag.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Index] = 1 then 1 else if [Flag] <> [Flag.1] then 1 else 0),
    Custom = #"Added Custom"[Custom],
    Custom1 = List.Generate(()=>[i=0,j= Custom{0},k=j],
                            each [i]<List.Count( Custom),
                            each [i=[i]+1,j=[j]+ Custom{i},k=j], 
                            each [k]),
    Combine = List.Combine({Table.ColumnNames(#"Added Custom"),{"desiredIndex"}}),
    new = Table.FromColumns(Table.ToColumns(#"Added Custom")&{Custom1},Combine),
    #"Removed Columns1" = Table.RemoveColumns(new,{"Index.1", "Flag.1", "Custom"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Index", Order.Ascending}})                   
in
    #"Sorted Rows1"

     

     

    • itsme's avatar
      itsme
      Icon for Resolver I rankResolver I
      4 years ago

      Thank you! It worked!

       

      Just a note, it takes a long time to load into PBI! But I was expecting that as I tested with other solutions that didn't work. My dataset has 8700 rows, which isn't even the full dataset. It took between 5-10 min to load. My original dataset has over 3M rows, which I will need to use after I finish building out this PBI. A little worried about that, fingers crossed! There is a decimal column that wasn't included in this example but is part of my dataset, which is what the flag is based on. I wonder if that's the main culprit. I would think that the datetime column takes up more memory.

       

      Either way, thank you!

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        itsme  it is a known fact that PQ has performance issue. On top of that, my solution involves do..while loop, which is going to delay processing even further. Your dataet is 3M+ rows. Are you getting it from some SQL db or some other RDBMS. If yes, it would be way wiser to see if you can achieve this server-side, rather than relying on PQ  for a 3 mil dataset. There are some known tweaks that you can refer to for PQ's performance tuning from ImkeF 's blog. But running a list.generate on a 3 mil ds is not going to be lightning fast for sure. I don't think DAX has the option of running a similar loop or else it would hzve been way quicker. You can keep this thread open and ask the DAX experts to take a look to see if it can be achievable. If that is not possible and if parry2k's solution meets your purpose, I petsonally think that would be a quicker option in your case. But I would strongly advise you to do this on server-side.