How to expand table with assigned distinctive column

[CornelisV]

Dear all,

Could you please demonstrate how to expand the tabel starting from this source:

Time	Valve1	Valve2
13-01-2024 13:20	0	1
13-01-2024 13:30	1	0
13-01-2024 13:40	1	0
13-01-2024 13:50	0	1
13-01-2024 14:00	0	1
13-01-2024 14:10	0	1
13-01-2024 14:20	0	1
13-01-2024 14:30	1	0
13-01-2024 14:40	0	1

The table should be expanded twice and a new column added to make a difference between two groups:

Time	Valve1	Valve2	Group
13-01-2024 13:20	0	1	1
13-01-2024 13:30	1	0	1
13-01-2024 13:40	1	0	1
13-01-2024 13:50	0	1	1
13-01-2024 14:00	0	1	1
13-01-2024 14:10	0	1	1
13-01-2024 14:20	0	1	1
13-01-2024 14:30	1	0	1
13-01-2024 14:40	0	1	1
13-01-2024 13:20	0	1	2
13-01-2024 13:30	1	0	2
13-01-2024 13:40	1	0	2
13-01-2024 13:50	0	1	2
13-01-2024 14:00	0	1	2
13-01-2024 14:10	0	1	2
13-01-2024 14:20	0	1	2
13-01-2024 14:30	1	0	2
13-01-2024 14:40	0	1	2

Thank you ands best regards,

Cornelis

[v-linyulu-msft · Accepted solution]

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.

[_AAndrade · Accepted solution]

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.

[_AAndrade]

Hi @CornelisV,

Take a look at my pbix file and see if this is what you're looking for.

[CornelisV]

Dear @_AAndrade ,

Thank you for your support. i have looked at the pbix file and it is not quite the solution. The original source is a table without column 'group'.

Although the table looks simple, actually the original format is > 10,000 rows and > 10 columns. I would like to import only in one time to save loading time into Power BI.

The question is: how can I expand the table starting from the original table above and result into table below without importing two times via ODBC.

Best regards,

[_AAndrade · Accepted solution]

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.

[CornelisV]

Hi @_AAndrade , thank you for your effort. This is exact what I'm looking for. I have already pasted in the Advanced Editor and understand the rule of game to implement.

Best regards, Cornelis

[_AAndrade · Accepted solution]

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

[amitchandak]

@CornelisV , Not very clear. Check if Field parameters can help

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

[CornelisV]

Hello @amitchandak ,

Does the explanation for @_AAndrade give you a reasonable insight?

Best regards,

[v-linyulu-msft · Accepted solution]

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.

[CornelisV]

Dear Leroy Lu,

That is an excellent explanition into details how the steps are done. Especially the append function is for me something new, but very common in SQL and other queries.

Thank you for you time and the provided links of Power BI training items.

Best regards,

Cornelis

[v-linyulu-msft]

Hi, @CornelisV 
No need to thank me, it’s my pleasure to assist you. You are an exceptionally understanding and polite user. Thank you for sharing the issues you encountered with us, and we’re delighted to see that your problem has been resolved.

If you have any further questions, please feel free to return to the forum and ask.

Wishing you a wonderful day!

Best Regards,

Leroy Lu