keep only the duplicated numbers in RemoteID Column when MediaType Column contains value =6

[Anonymous]

Hi everyone,

I am having some difficulties figuring out how to shape my table the way I would like to with power Query.

I would like to keep only the duplicated  numbers in RemoteID Column when MediaType Column contains at least  value =6  and 0 and then compute the difference in time between the first line and 2nd line  for 2 columns as showed in picture below.

Thank you in advance

[Anonymous]

Hi everyone,

I am having some difficulties figuring out how to shape my table the way I would like to with power Query.

I would like to keep only the duplicated  numbers in RemoteID Column when MediaType Column contains at least  value =6  and 0 and then compute the difference in time between the first line and 2nd line  for 2 columns as showed in picture below.

Thank you in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

if I understand you correctly:

1. There may be different approaches to filtering duplicated data only, this one is based on the Table.Group and Table.CountRows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AciIwMQ0wjKjNWByhkj5EzQ5UwRcmZIckkgAXN9qJSFPoqEJUzc0AAhkQziG8JljKAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Filter = _t, #"Time 1" = _t, #"Time 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Time 1", type datetime}, {"Time 2", type datetime}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, Value.Type(#"Changed Type")}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] > 1))
in
    #"Filtered Rows"

The actual records also get packed into a new column called Data for further reference/use.

2. The difference between columns on different rows can be solved using indexes (as each column represent a list):

et
    Source = Table,
    a = Source{[Filter="a"]}[Data],
    Custom1 = List.Last(a[Time 2]) - List.First(a[Time 1]),
    Custom2 = a[Time 2]{1} - a[Time 1]{0}
in
    Custom2

Above two scenarios: LastRowColumn2 - Row1Column1 and FirstRow Row2Column2 - Row1Column1. Select which one is more suitable for you.

3. Getting everything together:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AciIwMQ0wjKjNWByhkj5EzQ5UwRcmZIckkgAXN9qJSFPoqEJUzc0AAhkQziG8JljKAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Filter = _t, #"Time 1" = _t, #"Time 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Time 1", type datetime}, {"Time 2", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Count", each Table.RowCount(_), type number}, {"Diff", each _[Time 2]{1} - _[Time 1]{0}, type duration}, {"Data", each _, Value.Type(#"Changed Type")}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1))
in
    #"Filtered Rows"

Sorry, just have understood that I missed the 6+0 filtering :(. If you need help with adding it to the code, please let me know and I will update the post.

Kind regards,

JB

[Anonymous · Accepted solution]

Hi @Anonymous ,

if I understand you correctly:

1. There may be different approaches to filtering duplicated data only, this one is based on the Table.Group and Table.CountRows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AciIwMQ0wjKjNWByhkj5EzQ5UwRcmZIckkgAXN9qJSFPoqEJUzc0AAhkQziG8JljKAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Filter = _t, #"Time 1" = _t, #"Time 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Time 1", type datetime}, {"Time 2", type datetime}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, Value.Type(#"Changed Type")}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] > 1))
in
    #"Filtered Rows"

The actual records also get packed into a new column called Data for further reference/use.

2. The difference between columns on different rows can be solved using indexes (as each column represent a list):

et
    Source = Table,
    a = Source{[Filter="a"]}[Data],
    Custom1 = List.Last(a[Time 2]) - List.First(a[Time 1]),
    Custom2 = a[Time 2]{1} - a[Time 1]{0}
in
    Custom2

Above two scenarios: LastRowColumn2 - Row1Column1 and FirstRow Row2Column2 - Row1Column1. Select which one is more suitable for you.

3. Getting everything together:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AciIwMQ0wjKjNWByhkj5EzQ5UwRcmZIckkgAXN9qJSFPoqEJUzc0AAhkQziG8JljKAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Filter = _t, #"Time 1" = _t, #"Time 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filter", type text}, {"Time 1", type datetime}, {"Time 2", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Filter"}, {{"Count", each Table.RowCount(_), type number}, {"Diff", each _[Time 2]{1} - _[Time 1]{0}, type duration}, {"Data", each _, Value.Type(#"Changed Type")}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1))
in
    #"Filtered Rows"

Sorry, just have understood that I missed the 6+0 filtering :(. If you need help with adding it to the code, please let me know and I will update the post.

Kind regards,

JB

[Anonymous]

Hi jborro,

Thank you for your reply, it is partly solve my issue. 🙂

What I would like to do is,

1-) to get rid of the duplicate rows in column RemoteID_shaped that have only the value 0 in the column MediaType.

2-) to get rid of the non-duplicate rows of the column RemoteID_shaped.

3-) to kep only the duplicate number in column RemoteID_shaped that have value 6 then 0 (based on columns MediaType and InitiatedDateTime  Sort.Ascending)

the result should be kind this way:

your help will be appreciated.

Thank you in advance.

[Anonymous]

Hi @Anonymous

I do not have access to PBI at the moment, therefore can't test it (will do it later though). But if you expand the records that stored in the Data column and then filter to 6 only in the MediaType would this produce the outcome you want to achieve?

Thanks,
JB

[Anonymous]

Hello,

Thank you for your time.

this is awesome but it solves only one portion of my issue.  and the main part I would like to do is as follows:

3-) to kep only the duplicate number in column RemoteID_shaped that have value 6 then 0 (based on columns MediaType and InitiatedDateTime  Sort.Ascending)

and the result should be a kind of this way:

Thanks.

[Anonymous]

Hello,

Thank you for your time.

this is awesome but it solves only one portion of my issue.  and the main part I would like to do is as follows:

3-) to kep only the duplicate number in column RemoteID_shaped that have value 6 then 0 (based on columns MediaType and InitiatedDateTime  Sort.Ascending)

and the result should be a kind of this way:

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous ,

if you sort the data by Media type before grouping it should keep it this way after you expand the data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LDsAgCATQu7A24SNaOYvx/tewNFab0ITFEF6G3oEggZC2rKLqCzHeI0+UFUfqUAPMB+oHxsZyYP2BVoxN/Hrhcg3D363sRUwYuyprccW8mSw2Jg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Media Type" = _t, RemoteID_shaped = _t, #"Time 1" = _t, #"Time 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time 1", type datetime}, {"Time 2", type datetime}, {"Media Type", Int64.Type}, {"RemoteID_shaped", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Media Type", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"RemoteID_shaped"}, {{"Count", each Table.RowCount(_), type number}, {"Diff", each _[Time 2]{1} - _[Time 1]{0}, type duration}, {"Data", each _, Value.Type(#"Sorted Rows")}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Media Type", "Time 1", "Time 2"}, {"Media Type", "Time 1", "Time 2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Count", "Diff"})
in
    #"Removed Columns"

Kind regards,

John