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.
Hi Guys,
I have this table coming from a csv. The table has trajectories over x,y,z points and start and finish instants with and index column.
Each index (trackedFaceID) has multiple (x,y,z) points with duration (start and finish instants) spread over multiple columns, I want to have only four columns ("id", "duration", "x", "y","z")
You can download trajectories csv
Solved! Go to Solution.
Here you go something like this:
let
Source = Excel.Workbook(File.Contents("C:\Users\artemus\Downloads\trajectories.xlsx"), null, true),
in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(in_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"trajectoryId", Int64.Type}, {"trackedFaceID", Int64.Type}, {"beginInstant", type number}, {"endInstant", type number}, {"duration", Int64.Type}, {"trajectoryIntervals", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "trajectoryIntrevals_Json", each "{" & [trajectoryIntervals] & "}"),
#"Inserted Parsed JSON" = Table.AddColumn(#"Added Custom", "JSON", each Json.Document([trajectoryIntrevals_Json])),
#"Expanded JSON" = Table.ExpandRecordColumn(#"Inserted Parsed JSON", "JSON", {"trajectoryIntervals"}, {"trajectoryIntervals.1"}),
#"Expanded trajectoryIntervals.1" = Table.ExpandListColumn(#"Expanded JSON", "trajectoryIntervals.1"),
#"Expanded trajectoryIntervals.2" = Table.ExpandRecordColumn(#"Expanded trajectoryIntervals.1", "trajectoryIntervals.1", {"s", "e", "x", "y", "z"}, {"s", "e", "x", "y", "z"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded trajectoryIntervals.2",{"trajectoryId", "duration", "x", "y", "z"})
in
#"Removed Other Columns"
The key is to add {} around your trajectory column so that you can parse the entries as JSON
Here you go something like this:
let
Source = Excel.Workbook(File.Contents("C:\Users\artemus\Downloads\trajectories.xlsx"), null, true),
in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(in_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"trajectoryId", Int64.Type}, {"trackedFaceID", Int64.Type}, {"beginInstant", type number}, {"endInstant", type number}, {"duration", Int64.Type}, {"trajectoryIntervals", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "trajectoryIntrevals_Json", each "{" & [trajectoryIntervals] & "}"),
#"Inserted Parsed JSON" = Table.AddColumn(#"Added Custom", "JSON", each Json.Document([trajectoryIntrevals_Json])),
#"Expanded JSON" = Table.ExpandRecordColumn(#"Inserted Parsed JSON", "JSON", {"trajectoryIntervals"}, {"trajectoryIntervals.1"}),
#"Expanded trajectoryIntervals.1" = Table.ExpandListColumn(#"Expanded JSON", "trajectoryIntervals.1"),
#"Expanded trajectoryIntervals.2" = Table.ExpandRecordColumn(#"Expanded trajectoryIntervals.1", "trajectoryIntervals.1", {"s", "e", "x", "y", "z"}, {"s", "e", "x", "y", "z"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded trajectoryIntervals.2",{"trajectoryId", "duration", "x", "y", "z"})
in
#"Removed Other Columns"
The key is to add {} around your trajectory column so that you can parse the entries as JSON
Thanks! It worked just fine. Nice trick!
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.