Hi,(First of all, sorry i don't know how to create a box for my code to make it look nicer and also english isn't my native language)I don't know if it's a bug or if it's me not understanding how to concatenate strings or how it works but when i try this code to create a column : VAR maxname = LEN([People])//max size possible for each nameVAR loop = GENERATESERIES(1; maxname) //generate table with maxname rowsVAR loop1 = ADDCOLUMNS(loop; "Id"; UNICODE(RIGHT(LEFT([People];[Value]);1)))//put in each row : capitalize letters, spaces and "-" in their unicode number valueVAR test1 = CONCATENATEX(loop1;IF([Id]>=65 && [Id]<=90 || [Id]=32 || [Id]=45;UNICHAR([Id]);""))//concatenate every rows in their unicode string valueWhen i do RETURN test1, for one of my [People] the result "SERAY A" (this case is OK)BUT RETURN test1 &" "& test1 or CONCATENATE(test1&" ";test1), the same [People] gives me "E AASRY E AASRY" which means that suddently test1 is "E AASRY" and not "SERAY A"Thank you for the help

Hi Maradh, If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly. Please do mask sensitive data before uploading. Thanks for your understanding and support.Best Regards,Zoe Zhi If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Maradh

Helper I

6 years ago

Solved

Is concatenate broken ?

Hi,

(First of all, sorry i don't know how to create a box for my code to make it look nicer and also english isn't my native language)

I don't know if it's a bug or if it's me not understanding how to concatenate strings or how it works but when i try this code to create a column :

VAR maxname = LEN([People])

//max size possible for each name

VAR loop = GENERATESERIES(1; maxname)

//generate table with maxname rows

VAR loop1 =

ADDCOLUMNS(

loop;

"Id";

UNICODE(

RIGHT(

LEFT([People];[Value]);

)

//put in each row : capitalize letters, spaces and "-" in their unicode number value

VAR test1 =

CONCATENATEX(

loop1;

IF(

[Id]>=65 && [Id]<=90 || [Id]=32 || [Id]=45;

UNICHAR([Id]);

)

)
//concatenate every rows in their unicode string value

When i do RETURN test1, for one of my [People] the result "SERAY A" (this case is OK)

BUT RETURN test1 &" "& test1 or CONCATENATE(test1&" ";test1), the same [People] gives me "E AASRY E AASRY" which means that suddently test1 is "E AASRY" and not "SERAY A"

Thank you for the help

Need Help

dax
6 years ago
Hi Maradh,

If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dax

6 years ago

Hi Maradh,

This might be caused the temp table in your expression, so I suggest you could return test1 , then use measure like below to get result

measure=LEFT(MIN(T6[ID_data]),6) &"|"&RIGHT(MIN(T6[ID_data]),6)

Or you could use M code to get upper case like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31Dc0UNJR8vJ3DQ5xDFLwys9LLMlIzAMKmQKBUqwOVJ0RRJ1TkKOfi7+CS2Y+RIWJUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Employe = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employe", type text}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList([Employe])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=Text.Upper([Custom]) then [Custom] else null),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Employe"}, {{"com", each Text.Combine([Custom.1]), type text}, {"all", each _, type table [Date=date, Employe=text, ID=number, Custom=text, Custom.1=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "ID"}, {"all.Date", "all.ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded all")
in
    #"Removed Duplicates"

Then use above meausre.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

Maradh
Helper I
6 years ago
OK so i retry few hours later with the same code and without restarting PBI Desktop, and now it is working well but im still blocked with the same problem but with LEFT(test1; 6) and RIGHT(test1; 6)

LEFT(test1; 6) give me "SERAY" (OK)

RIGHT(test1; 6) give me "ERAY A" (OK)

But LEFT(test1;6) & " " & RIGHT(test1; 6) give me "SERAAY ERAAY" (Not OK, should be "SERAY ERAY A")
- dax
  Community Support
  6 years ago
  Hi Maradh,
  
  If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.
  
  Please do mask sensitive data before uploading.
  
  Thanks for your understanding and support.
  Best Regards,
  Zoe Zhi
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- dax
  Community Support
  6 years ago
  Hi Maradh,
  
  I don't know the [log_ID] in your sample, and I am not sure whether this wil cause this problem. I think you could try to check the value return from test1(you could try to return test1 in expression) or you could upload your sample(pbix) to me, then I will test this in my environment.
  
  Best Regards,
  Zoe Zhi
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- dax
  Community Support
  6 years ago
  Hi Maradh,
  
  This might be caused the temp table in your expression, so I suggest you could return test1 , then use measure like below to get result
  
  measure=LEFT(MIN(T6[ID_data]),6) &"|"&RIGHT(MIN(T6[ID_data]),6)
  
  Or you could use M code to get upper case like below
  
  let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31Dc0UNJR8vJ3DQ5xDFLwys9LLMlIzAMKmQKBUqwOVJ0RRJ1TkKOfi7+CS2Y+RIWJUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Employe = _t, ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employe", type text}, {"ID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList([Employe])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=Text.Upper([Custom]) then [Custom] else null), #"Grouped Rows" = Table.Group(#"Added Custom1", {"Employe"}, {{"com", each Text.Combine([Custom.1]), type text}, {"all", each _, type table [Date=date, Employe=text, ID=number, Custom=text, Custom.1=text]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "ID"}, {"all.Date", "all.ID"}), #"Removed Duplicates" = Table.Distinct(#"Expanded all") in #"Removed Duplicates"
  
  Then use above meausre.
  
  Best Regards,
  Zoe Zhi
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Is concatenate broken ?

4 Replies

Related content

Concatenate lists ;

Concatenated Lookup

concatenate strings

Line chart broken

Group and Concatenate