append columns

[PSB]

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

Before

S_NodeId	S_Cell1	S_Cell2	S_Cell3	T_gnb	T_cid	T_nRPCI	T_nRTAC
MNJ06941A2	ANJ06941A31	ANJ06941A21	ANJ06941A11	1314481	3	236	5893120
MNJ06941A2	ANJ06941A11	ANJ06941A31	ANJ06941A21	1314481	2	418	5893120
MNJ06941A2	ANJ06941A21	ANJ06941A11	ANJ06941A31	1314481	1	375	5893120
MNJ06941A2	ANJ06941A21			1343232	301	546	5893120
MNJ06941A2	ANJ06941A21			1327593	301	231	5893120
MNJ06941A2	ANJ06941A21			1343690	303	647	5893120
MNJ06941A2	ANJ06941A21			1343690	302	676	5893120

After

S_NodeId	S_Cell1,2,3	T_gnb	T_cid	T_nRPCI	T_nRTAC
MNJ06941A2	ANJ06941A31	1314481	3	236	5893120
MNJ06941A2	ANJ06941A11	1314481	2	418	5893120
MNJ06941A2	ANJ06941A21	1314481	1	375	5893120
MNJ06941A2	ANJ06941A21	1343232	301	546	5893120
MNJ06941A2	ANJ06941A21	1327593	301	231	5893120
MNJ06941A2	ANJ06941A21	1343690	303	647	5893120
MNJ06941A2	ANJ06941A21	1343690	302	676	5893120
MNJ06941A2	ANJ06941A21	1314481	3	236	5893120
MNJ06941A2	ANJ06941A31	1314481	2	418	5893120
MNJ06941A2	ANJ06941A11	1314481	1	375	5893120
MNJ06941A2	ANJ06941A11	1314481	3	236	5893120
MNJ06941A2	ANJ06941A21	1314481	2	418	5893120
MNJ06941A2	ANJ06941A31	1314481	1	375	5893120

[PSB]

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.

[Greg_Deckler]

@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"

[PSB]

Thanks! You are a Magician. 

[Greg_Deckler · 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.

[Greg_Deckler]

@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()
)