Forum Discussion

preethi's avatar
preethi
New Member
9 years ago

How to remove duplicate records

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 Sensor...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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