Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

0

Bug in CONCATENATEX?

Steps to re-create on April and May versions. PBIX is attached.

 

OK, this is bizarre but there is some strange behavior going on with CONCATENATEX. Steps to recreate:

 

1. Create the following tables in Power Query, Table, Table2 and Table3:

Table:

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1QEySnMyU8GsgMRcpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}})
in
    #"Changed Type"

 

 

 

You will have in your table:

John

Julie

Pam

 

Table2:

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlWK1YlW8srPyIMwSnMyIUIBiblKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}})
in
    #"Changed Type"

 

 

 

You will have in your table:

Mike

John

Julie

Pam

 

Table3:

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFOK1QEySnMyU8GsgMRcMO2bmQ0UiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}})
in
    #"Renamed Columns"

 

 

 

You will have in your table:

John

Julie

Pam

Mike

 

2. Now create the following column in each table:

 

 

 

Name Again = 
    VAR __Value = [Name]
    VAR __Table = 
            ADDCOLUMNS(
                GENERATESERIES(1,LEN(__Value),1),
                "Char",MID(__Value,[Value],1)
            )
RETURN
    CONCATENATEX(__Table,[Char],,",")

 

 

 

 

In Table, "Name Again" column for Pam is "Pam".

 

In Table2, "Name Again" column for Pam is "PaM"

 

In Table3, "Name Again" column for Mike is "mike"

 

So, it is as if somehow CONCATENATEX is "remembering" the value of lower or upper case letter from a previous iteration?????

Status: Delivered
Comments
v-qiuyu-msft
Community Support

Hi @Greg_Deckler

 

Thank you for reporting this issue. I have reported it internally: CRI 189082812. Will keep you update here once I get any new information. 

 

Best Regards,
Qiuyun Yu

v-qiuyu-msft
Community Support
Status changed to: Accepted
 
v-qiuyu-msft
Community Support

Hi @Greg_Deckler

 

Thank you for reporting this issue. I have reported it internally: CRI 189082812. Will keep you update here once I get any information. 

 

Best Regards,
Qiuyun Yu

v-diye-msft
Community Support
Status changed to: New

Hi @Greg_Deckler 

 

Kindly check the comments from PG team:

 

After thorough investigation, it is supposed that this issue is by design. PowerBI does not provide customer with choice of case sensitivity. Right now, PowerBI is case insensitive. Under current aggregation function(in this case, CONCATENATEX) execution plan, the engine will build a data cache for all letters it met before, and later on will directly get data from this cache instead of from data source.

However, since case insensitivity, two strings that differ only by case are treated as equal therefore whichever appeasr first will be stored in the cache. For example in this issue, Upper-case letter 'M' in "Mike" was stored first, during next iteration of "Pam",  'm', will hit the data cache of 'M', hence causing the final answer to be "PaM". 

 

v-diye-msft
Community Support
Status changed to: Delivered