Forum Discussion
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 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 |
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 ToTableHi
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
TransformStéphane
2 Replies
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 ToTableHi
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
TransformStéphane