How to remove duplicate records

[preethi]

I connected to Cassandra DB and get the Data using API call, able to create the table. However, wanted to know how to remove the duplicate records.

DateTime	MessageID	GatewayID	Name	SensorType	Value	IsBreached
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Light	0	FALSE
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Humidity	45	FALSE
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Temperature	24	FALSE
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Battery	2556	FALSE
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Shock	1000	FALSE
2017-02-08T17:06:01+01:00	45345	243234234	Data1	Tilt	180	TRUE

Expected: 

DateTime

MessageID

GatewayID

Name

Light

IsBreached

Humidity

IsBreached

Temperature

IsBreached

Battery

IsBreached

Shock

IsBreached

Tilt

IsBreached

2017-02-08T17:06:01+01:00

ca6acf53-c68d-4714-930d-03ee5e9f4e21

17007D8063

SensorBreach

0

FALSE

45

FALSE

24

FALSE

2256

FALSE

1000

FALSE

180

FALSE

[v-huizhn-msft]

Hi @preethi,

Based on the article @ImkeF posted, I try to reproduce your scenario using Unpivot columns and Merge function in Edit Query catelog.

This is my Power Query Statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZG9CsIwEIDfJast3OWnLd0UFYdONk6lQ9DQBi1KuA6+vZc+ggQCX4aPj+NuGIQErEuQJTQW6xaqFnAH2AKIQmijtGFKraTS/Ph/dOSQ2YVpJmbyzvuuP4mx+L92WZfwCPTdtCxF65ePj47W6DcjS/TgiHxMU0pjqizJfn7fn0wEyLNKG17pLtgk0V5vXBt/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t, MessageID = _t, GatewayID = _t, Name = _t, SensorType = _t, Value = _t, IsBreached = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetimezone}, {"MessageID", Int64.Type}, {"GatewayID", Int64.Type}, {"Name", type text}, {"SensorType", type text}, {"Value", Int64.Type}, {"IsBreached", type logical}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"DateTime", "MessageID", "GatewayID", "Name", "SensorType", "Value"}, "Attribute", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"SensorType", "Attribute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"DateTime", "MessageID", "GatewayID", "Name", "Merged", "Value.1", "Value"})
in
    #"Reordered Columns"

And get the followng result.



We're unable to transfer the part of column to Rows in Power BI desktop. All the columns will be transfered when I click "Transpose". Thanks for understanding.

Best Regards,
Angelia

[Phil_Seamark]

In the Query Editor, select the columns you want to remove duplicates and this should work for  you.

[preethi]

The data is avaliable as Name value pair in the columns, please check the data again for more clarity.

[ImkeF]

In order to achieve what you've showed you don't have to remove duplicates, but to pivot your data on a pair of columns. I've described the technique here:

http://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

Pls let me know if you need help implementing this.