Forum Discussion

fedpar's avatar
fedpar
Microsoft Employee
10 years ago
Solved

Custom column Index or Ranking by other column

I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is: Group      Date A 18-Apr A 19-Apr A 23-Apr...
  • ImkeF's avatar
    ImkeF
    10 years ago

    Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

     

    let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"
ImkeF's avatar
ImkeF
Community Champion
10 years ago

Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

 

let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"
  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    Imke

     

    nice?  Is this hand coded?  If so, can you explain it?  Is is like a partition over clause in TSQL?

    • ImkeF's avatar
      ImkeF
      Community Champion
      10 years ago

      Hi Matt,

      good point – it’s handwritten. Actually in this case we wouldn’t have to nest it in but could also have added a new column with a handwritten Table.AddIndexColumn-command instead. Or a separate function. Might be a matter of taste at the end. (But I’d recommend to delete the other column before expanding).

       

      let

    Source = Table1,

    Partition = Table.Group(Source, {"Group"}, {{"Partition", each _, type table}}),

    #"Added Custom" = Table.AddColumn(Partition, "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Partition"}),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Index"}, {"Date", "Index"})

in

    #"Expanded Custom"

       

       

      What many people don’t know about is the syntax sugar around “each” and that the last argument of Table.AddColumn takes in a function. So no need to write “() =>”, just take “each”.

       

      I’ve given this technique the name partition, because it returns the same like the PARTITION OVER and I prefer catchy names (but of course this could also lead to confusion). But if applied to a SQL-source it will be executed as GROUP BY (if it comes with a statement that would fold, like SUM or AVERAGE – in our example here with an Index it needs to return all row, so no folding would take place on the server).

       

      But still: This technique is a performance saver when it comes to iterative operations like running totals (don't Table.SelectColumns (equivalent to WHERE) - because they would always iterate over the whole table!!). But if you are querying SQL-sources a native PARTITION OVER SQL-query would be even faster.

      • sebbyp's avatar
        sebbyp
        Helper III
        10 years ago

        I want to use this code that you have kindly provided in this thread to solve the same issue I am experiencing.  However, I am new to power bi and i cannot get it to work.  This is what i have done already and now i want to slot your code in to add this indexed column.  The only difference is "Group" would be "Ticker" in my case.  Can you help please? 

         

        let
    Source = Excel.Workbook(File.Contents("C:\Users\Exceldata.xlsx"), null, true),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Expanded Column2" = Table.ExpandTableColumn(#"Promoted Headers", "Column2", {"Column1", "Column10", "Column11", "Column12", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column2.Column1", "Column2.Column10", "Column2.Column11", "Column2.Column12", "Column2.Column2", "Column2.Column3", "Column2.Column4", "Column2.Column5", "Column2.Column6", "Column2.Column7", "Column2.Column8", "Column2.Column9"}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Column2"),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([Date] <> "Date")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"GDP CQOQ Index", "Ticker"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"GDP CQOQ Index_1", "Column16", "Sheet"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","#N/A N/A"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#N/A Field Not Applicable"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value2",{{"Date", type date}, {"PX_LAST", type number}, {"ACTUAL_RELEASE", type number}, {"FIRST_REVISION", type number}, {"BN_SURVEY_LOW", type number}, {"BN_SURVEY_AVERAGE", type number}, {"BN_SURVEY_MEDIAN", type number}, {"BN_SURVEY_HIGH", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "BN_Surevy Range", each [BN_SURVEY_HIGH]-[BN_SURVEY_LOW]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"BN_Surevy Range", type number}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "BN_Surevy Range", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"BN_Surevy Range", "BN_Survey Range"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Actual Vs Consensus", each [ACTUAL_RELEASE]-[BN_SURVEY_MEDIAN]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"ECO_RELEASE_DT", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each Date.FromText([ECO_RELEASE_DT])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Eco Release Date"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"ECO_FUTURE_RELEASE_DATE", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type4", "Eco Future Release Date", each Date.FromText([ECO_FUTURE_RELEASE_DATE])),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"Eco Future Release Date", type date}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Changed Type5",{{"FIRST_REVISION_DATE", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type6", "First Revision Date", each Date.FromText([FIRST_REVISION_DATE])),
    #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom4",{{"First Revision Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type7",{"FIRST_REVISION_DATE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each true)
in
    #"Filtered Rows1"

         

  • javix72's avatar
    javix72
    New Member
    9 years ago

    I have used this procedure succefully to obtian rankings based on groups, however, when mergeing with another table to gather detailed information, upon expanding the Table Column, the resulting values get scrambled. Only those generated using the Group and Index procedure... Could it be a Power Query Bug?

    • ImkeF's avatar
      ImkeF
      Community Champion
      9 years ago

      It might help if you buffer the group-step (Table.Buffer).

      Otherwise I would need more detailled information of how the scramble looks like/what exactly is the problem.

      • javix72's avatar
        javix72
        New Member
        9 years ago

        Thank you so much! It worked fine. After using the Table.Buffer it worked perectly...!

  • pandakillsalot's avatar
    pandakillsalot
    Helper II
    8 years ago

    Is there any chance to implement your beautiful solution in direct query model?

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      Unfortunately this doesn't work in Direct Query mode and I can also not think of a workaround unfortunately.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, it looks like the exact solution that I am looking for but I not so well-versed in Power Query and unable to follow how to implement it in my work. Can someone show it in steps? For a start I have a table that I want to index based upon Week-ending date.

    Thank you so much. 

  • SDittmannFleet's avatar
    SDittmannFleet
    Advocate IV
    2 years ago

    ImkeF this is brilliant, exactly what I was looking for!