Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
In the Query Editor, select the columns you want to remove duplicates and this should work for you.
The data is avaliable as Name value pair in the columns, please check the data again for more clarity.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |