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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors