Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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]) )
This calculated column can be replaced by Power Query as below
Best Regards
Maggie
basically do the following
1) add a custom column
{[Phase1],[Phase2]}
2) expand rows in that column
3) chagne type to text
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"
this would be much easier to do in PowerQuery - is it a must to have Phase1/Phase2 created in DAX?
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]) )
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.
This calculated column can be replaced by Power Query as below
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
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"
Thank you all for the help! I managed to make it work with your help!
can you paste the sample rows from Merge1 table?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |