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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CornelisV
Helper I
Helper I

How to expand table with assigned distinctive column

Dear all,

 

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

TimeValve1Valve2
13-01-2024 13:2001
13-01-2024 13:3010
13-01-2024 13:4010
13-01-2024 13:5001
13-01-2024 14:0001
13-01-2024 14:1001
13-01-2024 14:2001
13-01-2024 14:3010
13-01-2024 14:4001

 

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

 

TimeValve1Valve2Group
13-01-2024 13:20011
13-01-2024 13:30101
13-01-2024 13:40101
13-01-2024 13:50011
13-01-2024 14:00011
13-01-2024 14:10011
13-01-2024 14:20011
13-01-2024 14:30101
13-01-2024 14:40011
13-01-2024 13:20012
13-01-2024 13:30102
13-01-2024 13:40102
13-01-2024 13:50012
13-01-2024 14:00012
13-01-2024 14:10012
13-01-2024 14:20012
13-01-2024 14:30102
13-01-2024 14:40012

 

Thank you ands best regards,

 

Cornelis

3 ACCEPTED SOLUTIONS

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):

vlinyulumsft_0-1730355388674.png

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

vlinyulumsft_1-1730355388675.png

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

vlinyulumsft_2-1730355408367.png

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

vlinyulumsft_3-1730355408371.png

vlinyulumsft_6-1730355467136.png

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:

vlinyulumsft_7-1730355670213.png

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:

vlinyulumsft_8-1730355736713.png

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.

 

View solution in original post

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.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

10 REPLIES 10
_AAndrade
Super User
Super User

Hi @CornelisV,

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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'.

source.png

 

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.

source2.png

 

Best regards,

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.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




amitchandak
Super User
Super User

@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

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hello @amitchandak ,

 

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

 

Best regards,

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):

vlinyulumsft_0-1730355388674.png

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

vlinyulumsft_1-1730355388675.png

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

vlinyulumsft_2-1730355408367.png

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

vlinyulumsft_3-1730355408371.png

vlinyulumsft_6-1730355467136.png

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:

vlinyulumsft_7-1730355670213.png

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:

vlinyulumsft_8-1730355736713.png

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.

 

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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