Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Equivalent to a new column usign OVER functionality

Two columns : one is an ID column, with non-unique values.  The second column SIZES contains integers.  I want to create a third column with the maximum value of SIZES for each value of ID, without d...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ,

     

    Using below M code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlQ4tACOlHSUIEwDpVgdXHImeOTMwXJGeMzEKRcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NUMS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NUMS", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let i = [ID]
in
Table.Max(Table.SelectRows(#"Changed Type",each [ID] = i),"NUMS")[NUMS])
in
    #"Added Custom"

     

    And you will see:

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    6 years ago

    I've come up with a performant solution - the ones so far did not work well on 120k rows.  Basically, I created a (temporary) table of the grouped data, then merged this back into the main table.  Something like this:

    let

        Source = whatever

        Grouped = Table.Group(#"Source", col1, max(number))

        Merged = Table.Merge(#"Source", #"Grouped", using col1)

    Because this uses table processing rather than row processing, it runs pretty quickly.

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi  Anonymous ,

 

As line 4 and line 5 have the same values in each column,so when the data is put in a table visual ,you need to create an index column first,otherwise the 2 lines will be aggregated:

Go to query editor>add column>index column;

Then create a measure as below:

 

Measure = MAXX(FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])),'Table'[  NUMS])

 

And you will see: 

 

Or you can create a calculated column as below:

 

Column = CALCULATE(MAX('Table'[  NUMS]),ALLEXCEPT('Table','Table'[ID]))

 

And you will see:

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Is it possible to do the same in M, so that I can push it into a Dataflow ?

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Using below M code:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlQ4tACOlHSUIEwDpVgdXHImeOTMwXJGeMzEKRcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NUMS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NUMS", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let i = [ID]
in
Table.Max(Table.SelectRows(#"Changed Type",each [ID] = i),"NUMS")[NUMS])
in
    #"Added Custom"

       

      And you will see:

       

      For the related .pbix file,pls click here.

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I've come up with a performant solution - the ones so far did not work well on 120k rows.  Basically, I created a (temporary) table of the grouped data, then merged this back into the main table.  Something like this:

        let

            Source = whatever

            Grouped = Table.Group(#"Source", col1, max(number))

            Merged = Table.Merge(#"Source", #"Grouped", using col1)

        Because this uses table processing rather than row processing, it runs pretty quickly.