Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
plot
New Member

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 NoVoltage 1Resistance 1Test Date 1Voltage 2Resistance 2Test Date 2Voltage 3Resistance 3Test Date 3
S0515410.5310.33631/10/2023 08:3310.2710.04602/11/2023 11:0210.9160.16106/11/2023 11:34
S6056510.8520.49430/10/2023 15:5010.6190.93302/11/2023 11:52   
S3168110.9000.01531/10/2023 07:1210.2510.66603/11/2023 10:1910.0600.25807/11/2023 08:44
S6098810.9200.35031/10/2023 11:41      

 

plot_0-1711467089824.png

plot_1-1711467209250.png

plot_3-1711467281936.png

plot_4-1711467375950.png

 

 

2 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @plot, what about this?

 

Result

dufoq3_0-1711472608775.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

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 

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @plot, what about this?

 

Result

dufoq3_0-1711472608775.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors