Forum Discussion

plot's avatar
plot
New Member
2 years ago
Solved

Combine columns into tables

I have a set of data where each record has a 'Serial No' and 3 iterations of 'Voltage', 'Resistance', and 'Test Date'. I would like to plot the voltage changes over time for each serial number on a s...
  • dufoq3's avatar
    2 years ago

    Hi plot, what about this?

     

    Result

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZBBDoMwDAS/UnGuYNeOTZJv9Ij4/zcabAS0UiRH8sQ7zrZNHxitTO+JmE05LphVfVTlQiwC0RdqV01G1mRQDgaykMmQHZJMY/RmerD+ZLRM+3vEOswt8WoSeGmHh+KKpXVDMs4WTAuP39h4fp6YrfTKUwVIXdrfSmvnqSuWK7mHtt6z0SN2MPCcI1aPul7M+JpyrdRqPWMlcQ3/R+zQLbx1b+/9Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t, #"Voltage 1" = _t, #"Resistance 1" = _t, #"Test Date 1" = _t, #"Voltage 2" = _t, #"Resistance 2" = _t, #"Test Date 2" = _t, #"Voltage 3" = _t, #"Resistance 3" = _t, #"Test Date 3" = _t]),
        SplitParameter = List.Count(List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Voltage", Comparer.OrdinalIgnoreCase))),
        Transformed = List.TransformMany(Table.ToRows(Source),
            each List.Split(List.Skip(_), SplitParameter),
            (x,y)=> {x{0}} & y ),
        ToTable = Table.FromRows(
            List.Select(Transformed, (x)=> List.Count(List.Select(x, (y)=> not List.Contains({"", null}, Text.Trim(y)))) > 1), //filtered out rows with blank or null data from Transformed step
            {"Serial No"} & List.Transform(List.FirstN(List.Skip(Table.ColumnNames(Source)), SplitParameter), each Text.Trim(Text.Select(_, {"a".."z", "A".."Z", " "}))) //Column Names
        )
     //Column Names
    
    in
        ToTable

     

  • slorin's avatar
    slorin
    2 years ago

    Hi

    Another solution with Table.ToColumns

    let
    Source = Your_Source
    ToColumns = Table.ToColumns(Source),
    Transform =
    Table.Combine(
    List.Transform(
    {0,1,2},
    (y)=> Table.SelectRows(
    Table.FromColumns(
    {ToColumns{0},ToColumns{y*3+1},ToColumns{y*3+2},ToColumns{y*3+3}},
    {"Serial No","Voltage","Resistance","Test Date"}),
    each [Voltage] <> "")))
    in
    Transform

    Stéphane