How to expand table with assigned distinctive column

Thanks for the reply from _AAndrade and amitchandak , please allow me to provide another insight:

Hi, CornelisV 

1.Firstly, I should explain that, since your requirements involve data structure, we recommend completing this operation in Power Query. Therefore, _AAndrade 's proposed solution is also carried out in Power Query. Here’s my explanation of _AAndrade's approach, which I hope will help you understand:

Here's his raw data(FirstTable):

1.He duplicated the original query as a new query(SecondTable).

2.He added custom columns to both queries for grouping.

3.He then performed an append operation on the two tables.

As a result, the original data does not contain a group column; this was generated later through Power Query processing. Additionally, if you are performing an append operation, you do not need to worry about the number of columns, as long as the data structures are consistent, the append will be complete.

1.Furthermore, I made some adjustments to the M language based on his work, making it more convenient:

let
    // Load data from a compressed JSON format and create a table from it
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WCsnMTVXSUQpLzClLNYQxjJRidaKVDI11DQx1jQyMTBQMja2MDICyIGyIRdIYIgFSgClpgk/SFLexJlYG+CQN8Unica0JPteaQFwL1RkLAA==", 
                BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) 
        in type table [Column1 = _t, Column2 = _t, Column3 = _t]
    ),
    
    // Promote the first row to be the header of the table
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // Change the data types of specific columns
    #"Changed Type1" = Table.TransformColumnTypes(
        #"Promoted Headers", 
        {{"Time", type text}, {"Valve1", Int64.Type}, {"Valve2", Int64.Type}}
    ),
    
    // Add a new column named "group" with a constant value of 1
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "group", each 1),
    
    // Add another column named "group" with a constant value of 2
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "group", each 2),
    
    // Combine the two tables created by the previous steps
    #"Appended Query" = Table.Combine({#"Added Custom1", #"Added Custom2"}),
    
    // Change the data type of the "group" column to number
    #"Changed Type" = Table.TransformColumnTypes(#"Appended Query", {{"group", type number}})
in
    // Return the final table
    #"Changed Type"

2.Here are the final results, which I hope will meet your needs:

3.For further details, please refer to:

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn
Add a custom column in Power BI Desktop - Power BI | Microsoft Learn
Append queries - Power Query | Microsoft Learn
 

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi CornelisV,

In my solution the initial table don't have the group column as well. You can see this in the source step. I use a step on power query to add the group column.
I did some changes on my code and put all in one query, here is the M code:

let
    // Original Source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNTDUNTIwMlEwNLYyMlDSUQJhQ6VYHXRJY4gESAGmpAk+SVPcxppYGeCTNMQnice1JvhcawJxLVRnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, Valve1 = _t, Valve2 = _t]),
    ChangeType = Table.TransformColumnTypes(Source, {{"Time", type datetime}, {"Valve1", Int64.Type}, {"Valve2", Int64.Type}}),
    FirstTable = Table.AddColumn(ChangeType, "Group", each 1, Int64.Type),
    SecondTable = Table.AddColumn(ChangeType, "Group", each 2, Int64.Type),
    FinalTable = Table.Combine({FirstTable, SecondTable})
in
    FinalTable

 
As you can see you only need to import one time the source and the final result is what you put oin your example.
I hope this could help you.

Great. As my post solved your issue, please mark my post as a solution! Kudos are welcome, too 🙂
Thanks