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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Double the rows in order to add the values from another column

Hello,

 

I have the following table:

 

ID    Phase1     Phase2

1      P1              P2

2      P2              P3

3      P4               P4

 

and the output I would want:

 

ID    Phase1     Phase2      Phase

1      P1              P2             P1

1      P1              P2             P2          

2      P2              P3             P2

2      P2              P3             P3

3      P4              P4             P4

 

I mention the Phase1 & Phase2 were created by DAX.

Can someone help me out on this?

 

 

4 ACCEPTED SOLUTIONS

are you using Direct query or is there another reason?
with DAX you can create calculated table (see syntax below), but AFAIK you cannot modify the table itself to that format, you would need to do that in PowerQuery

Table 2 = 
UNION(
    ADDCOLUMNS('Table',"Phase",[Phase1]),
    ADDCOLUMNS('Table',"Phase",[Phase2])
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Hi @NewbiePowerBye

This calculated column can be replaced by Power Query as below

7.png

6.png

 

 

Best Regards

Maggie

 

View solution in original post

basically do the following

1) add a custom column

{[Phase1],[Phase2]}

2) expand rows in that column

3) chagne type to text

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

that would mean that the [Phase2] field's are records not text, is that the case?
this is the coe I tested with, and it expands as text

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjy0QElHKQBKGSnF6kQrGUE5EMoYLGYM4ZhAKaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, Phase1 = _t, Phase2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"Phase1", type text}, {"Phase2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Phase", each {[Phase1],[Phase2]}),
    #"Expanded Phase" = Table.ExpandListColumn(#"Added Custom", "Phase")
in
    #"Expanded Phase"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

11 REPLIES 11
Stachu
Community Champion
Community Champion

this would be much easier to do in PowerQuery - is it a must to have Phase1/Phase2 created in DAX?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

It is a must unfortunately 😞

This has no solution in DAX? 

are you using Direct query or is there another reason?
with DAX you can create calculated table (see syntax below), but AFAIK you cannot modify the table itself to that format, you would need to do that in PowerQuery

Table 2 = 
UNION(
    ADDCOLUMNS('Table',"Phase",[Phase1]),
    ADDCOLUMNS('Table',"Phase",[Phase2])
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

I I would want to modify the table itself to that format.

I can't do that because I can't recreate this dax created column into the editor:

 

Second = CALCULATE(MAX(Merge1[First]),ALLEXCEPT ( Merge1,Merge1[Key]))

 

If someone can help me translate it and have the same result in Custom column in power query editor would solve my problem.

Hi @NewbiePowerBye

This calculated column can be replaced by Power Query as below

7.png

6.png

 

 

Best Regards

Maggie

 

Thank you @v-juanli-msft i managed to recreate the calcuted columns in power query! 

@Stachu can you show me how to do my initial request in Power Query Editor? 

basically do the following

1) add a custom column

{[Phase1],[Phase2]}

2) expand rows in that column

3) chagne type to text

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

When I do this custom column it outputted a 'list' type of values. I then selected on the column 'Expand To New Rows'. Then I see Phase 1 value, but Phase2 value is shown as 'Record'. How can I replace 'Record' to show actual value?

that would mean that the [Phase2] field's are records not text, is that the case?
this is the coe I tested with, and it expands as text

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjy0QElHKQBKGSnF6kQrGUE5EMoYLGYM4ZhAKaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, Phase1 = _t, Phase2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"Phase1", type text}, {"Phase2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Phase", each {[Phase1],[Phase2]}),
    #"Expanded Phase" = Table.ExpandListColumn(#"Added Custom", "Phase")
in
    #"Expanded Phase"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thank you all for the help! I managed to make it work with your help! 

can you paste the sample rows from Merge1 table?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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