handling n number of columns

[reymalave]

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

[artemus · Accepted 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

[reymalave]

Thanks! It worked just fine. Nice trick!