Combine columns into tables

[plot]

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 single line chart.

The difficulty is that the voltages, resistances, and timestamps are in seperate columns - is there a better way to combine them? The data comes from a Sharepoint List.

Serial No	Voltage 1	Resistance 1	Test Date 1	Voltage 2	Resistance 2	Test Date 2	Voltage 3	Resistance 3	Test Date 3
S05154	10.531	0.336	31/10/2023 08:33	10.271	0.046	02/11/2023 11:02	10.916	0.161	06/11/2023 11:34
S60565	10.852	0.494	30/10/2023 15:50	10.619	0.933	02/11/2023 11:52
S31681	10.900	0.015	31/10/2023 07:12	10.251	0.666	03/11/2023 10:19	10.060	0.258	07/11/2023 08:44
S60988	10.920	0.350	31/10/2023 11:41

[dufoq3 · Accepted solution]

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

[dufoq3 · Accepted solution]

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 · Accepted solution]

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