cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PSB
Advocate I
Advocate I

append columns

How can I combine S_Cell1, S_Cell2, S_Cell3 into one column S_Cell1,2,3 and keep the other related column data with it.

Example shown below in Before and After

PSB_0-1664636971983.png

 

Before

S_NodeIdS_Cell1S_Cell2S_Cell3T_gnbT_cidT_nRPCIT_nRTAC
MNJ06941A2ANJ06941A31ANJ06941A21ANJ06941A11131448132365893120
MNJ06941A2ANJ06941A11ANJ06941A31ANJ06941A21131448124185893120
MNJ06941A2ANJ06941A21ANJ06941A11ANJ06941A31131448113755893120
MNJ06941A2ANJ06941A21  13432323015465893120
MNJ06941A2ANJ06941A21  13275933012315893120
MNJ06941A2ANJ06941A21  13436903036475893120
MNJ06941A2ANJ06941A21  13436903026765893120

 

After

S_NodeIdS_Cell1,2,3T_gnbT_cidT_nRPCIT_nRTAC
MNJ06941A2ANJ06941A31131448132365893120
MNJ06941A2ANJ06941A11131448124185893120
MNJ06941A2ANJ06941A21131448113755893120
MNJ06941A2ANJ06941A2113432323015465893120
MNJ06941A2ANJ06941A2113275933012315893120
MNJ06941A2ANJ06941A2113436903036475893120
MNJ06941A2ANJ06941A2113436903026765893120
MNJ06941A2ANJ06941A21131448132365893120
MNJ06941A2ANJ06941A31131448124185893120
MNJ06941A2ANJ06941A11131448113755893120
MNJ06941A2ANJ06941A11131448132365893120
MNJ06941A2ANJ06941A21131448124185893120
MNJ06941A2ANJ06941A31131448113755893120
1 ACCEPTED SOLUTION

@PSB This is a DAX calculated table. It is probably doable in Power Query as well, but that will take me a minute to figure out. Give me a few minutes.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
PSB
Advocate I
Advocate I

This is good solution. It will work but can it be done using tranformation in edit query?

or can this result be available in Edit Query window for applying more tranformations?

Sorry if I am not able to explain well. I'm new to Power BI.

@PSB OK, here is the Power Query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY9BDsIwDAS/gnLuIet1nOTYK1L5QNX/f4M6AkoQiAgOtrJ2PF6va1gu52hVMUuYwnwXxLOSTsEVCNXiL+4htD2nUgmJYZs+YYEvSw6szynKEPaNv37JgW2OcxrHnm4BKoXeZ/R60rGTXyCSU+UDIs3dD06sxgZxlGn+D+K/LHfnbFc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_NodeId = _t, S_Cell1 = _t, S_Cell2 = _t, S_Cell3 = _t, T_gnb = _t, T_cid = _t, T_nRPCI = _t, T_nRTAC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S_NodeId", type text}, {"S_Cell1", type text}, {"S_Cell2", type text}, {"S_Cell3", type text}, {"T_gnb", Int64.Type}, {"T_cid", Int64.Type}, {"T_nRPCI", Int64.Type}, {"T_nRTAC", Int64.Type}}),
    Column1 = Table.RenameColumns(Table.SelectColumns(#"Changed Type", { "S_NodeId", "S_Cell1", "T_gnb", "T_cid", "T_nRPCI", "T_nRTAC"}),{{"S_Cell1", "S_Cell1,2,3"}}),
    Column2 = Table.RenameColumns(Table.SelectColumns(#"Changed Type", { "S_NodeId", "S_Cell2", "T_gnb", "T_cid", "T_nRPCI", "T_nRTAC"}),{{"S_Cell2", "S_Cell1,2,3"}}),
    Column3 = Table.RenameColumns(Table.SelectColumns(#"Changed Type", { "S_NodeId", "S_Cell3", "T_gnb", "T_cid", "T_nRPCI", "T_nRTAC"}),{{"S_Cell3", "S_Cell1,2,3"}}),
    AppendTables = Table.Combine( { Column1, Column2, Column3}),
    #"Filtered Rows" = Table.SelectRows(AppendTables, each ([#"S_Cell1,2,3"] <> " "))
in
    #"Filtered Rows"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks! You are a Magician. 

@PSB This is a DAX calculated table. It is probably doable in Power Query as well, but that will take me a minute to figure out. Give me a few minutes.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@PSB Try:

Table 2 = 
FILTER(
    UNION(
        SELECTCOLUMNS('Table',"T_cid",[T_cid],"T_gnb",[T_gnb],"T_nRPCI",[T_nRPCI],"T_nRTAC",[T_nRTAC],"S_Cell1,2,3",[S_Cell1]),
        SELECTCOLUMNS('Table',"T_cid",[T_cid],"T_gnb",[T_gnb],"T_nRPCI",[T_nRPCI],"T_nRTAC",[T_nRTAC],"S_Cell1,2,3",[S_Cell2]),
        SELECTCOLUMNS('Table',"T_cid",[T_cid],"T_gnb",[T_gnb],"T_nRPCI",[T_nRPCI],"T_nRTAC",[T_nRTAC],"S_Cell1,2,3",[S_Cell3])
    ),
    [S_Cell1,2,3] <> BLANK()
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.