Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi there,
I have some difficulties to combine text columns with the result I want. If someone could help me to point me to the right direction 🙂
So I have this table :
Col1 Col2 Col3 Col4 Std Combine Wanted
A | B | C | D | A & B & C & D | A & B & C & D |
B | & B & & | B | |||
A | C | A & & C & | A & C | ||
A | A & & & | A | |||
B | D | & B & & D | B & D |
if I combine the columns with the std feature , you can see that empty ones are surrounded by unnecessary "&" char.
what I would like is the "Wanted" column result.
My ideas were to create a list of not empty columns and combine them but I'm not good enough to create a custom function for that...
Thanks.
Solved! Go to Solution.
Good catch Marcel but I have other columns 🙂
Thanks for your help, you pointed me to the right way !
here the solution :
= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))
Thanks again
Pleased you solved your own issue.
I'd rather had shortened the code a bit:
= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
My solution
Text.Combine(List.Select({[ddd_1],[telefone_1]}, each _<> "" and _ <> null)," - ")
each _<> "" and _ <> null
Can anyone point me to the meaning of each _<> "" and _ <> null?
I am trying to combine columns as well.
Thank you.
Laurel
If you have no other columns, you use the query editor to add a custom column with formula:
= Text.Combine(List.Select(Record.FieldValues(_), each _<> "" and _ <> null)," & ")
Good catch Marcel but I have other columns 🙂
Thanks for your help, you pointed me to the right way !
here the solution :
= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))
Thanks again
Pleased you solved your own issue.
I'd rather had shortened the code a bit:
= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
Hi All
Does anyone know how I can get this code to work only to show the unique entry Power bi desktop?
Table.AddColumn(#"Removed Columns1", "Combined Deps",
each Text.Combine(List.Select({[#"Assign Dept 1 "],[#"Assign Dept 2 "],[#"Assign Dept 3 "],[#"Assign Dept 4 "]},
each _<> "" and _ <> null),","))
As expected, I got duplicates.
I need to combine ten columns that could have essentially the same department assigned a task.I only want to see one unique entry if data is found.
thanks in advance
Do u know how to achieve the same result in DAX. I also go the same question. Since i am using direct query I cannot do any transormations using M query.
Regards,
Maddy
Hello MacelBeug
Would you be so kind, if possible, to give me a hint to solve my problem. It is very close to the one you solved here. There's no concatenation, and it is very close to the problem of union of sets in Set Theory...
There are sometimes I get the value for a key column using some join. Sometimes you look in serveral sources to get the values. In some sources you get the value but not in others.... At the end you only wish a column with a key value
Well I believe the best is to show a sample. I would like to get the red values in "column e"
Thank you very much in advance
Hi,
Here is the M code i used
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}, {"Text4", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Text1", "Text2", "Text3", "Text4"}),
#"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "Merged", each Text.Combine({[Text1], [Text2], [Text3], [Text4]}, ""), type text),
#"Split Column by Position" = Table.SplitColumn(#"Inserted Merged Column", "Merged", Splitter.SplitTextByPositions({0, 1}, false), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1", type text}, {"Merged.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2"})
in
#"Removed Columns"
Thank you very much! It works fine! I'm trying to learn M, and this a is a very good and really useful example!
You are welcome. If my reply helped, please mark it as Answer.
I have run yor M code and it works, but when I analyzed the code I realized you took advantange that every cell with data in my table has lenght just one (it just for a brief example) and you split column by position. In this case position "1" always works.
Imagine that in the cells of a certain row were just a word like "Luke", now the split position would be the length of the word "Luke". In the other rows could be another different length...
I suppose that an algorithm following your idea would be: calculate the maximum of the lenghts of each items on each row, maybe you have to use a dummy column with a value to concatenate further.... or maybe just a different approach to solve the problem!
Hi,
There should be a solution to that as well. Please take a practical example and show the expected result.
Here you have. It is the same than the previous one but just using words of different lenght in the rows. Before I only used characters of lenght 1...
Hi,
Try this M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Name2", type text}, {"Name3", type text}, {"Name4", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Name1], [Name2], [Name3], [Name4]}, ";"), type text),
#"Trimmed Text" = Table.TransformColumns(#"Inserted Merged Column",{{"Merged", Text.Trim}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged.1", "Result"}})
in
#"Renamed Columns"
Hope this helps.
Of course it helps! It works! I shows me the power of the delimeters! Thank you very much!
Hi,
You are welcome. Please mark my reply as Answer.
I cannot see where to mark your reply as an answer.. In the place where there are 3 points in a row there is not such option in the scroll down menu... maybe it's only allowed just one "solved" per post (thread)
Yes you are right. I'm frustrated how it seems so easy 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |