Convert Rows to column and group in new column

[SteveDesmedt]

Hi all, 

I'm struggling with following scenario :

Table1

Entry No.	Document Code	Document Reference
1	B00	12345677
2	B00	AER445ER
3	C00	IUYYN/1225
1	C00	DEEFFD25
1	B00	4545878787
3	C00	FFROIIS/445
3	B00	44558877

What i want to achieve is one row per entry no. and combination of document code and document reference in one column per entry no.

Expected result

Entry No	Document + Reference
1	B00 : 12345677 & 4545878787 - C00 : DEEFFD25
2	B00 : AER445ER
3	B00 : 44558877 - C00 : IUYYN/1225 & FFROIIS/445

An additional complixity is to build following string B00 : 12345677 & 4545878787 - C00 : DEEFFD25

Can anybody help me ?

Thanks in advance

[slorin]

Hi,

And a Power Query solution

let
Source = YourSource,
Type = Table.TransformColumnTypes(Source,{{"Document Reference", type text}}),
Group = Table.Group(
   Type, 
   {"Entry No."}, 
   {{"Data", each Table.Group(
        _, 
        {"Document Code"},
        {{"Reference", each Text.Combine([Document Reference], " & ")}}
      )
   }}),
#"Doc+Ref" = Table.AddColumn(Group, "Document + Reference", each 
       Text.Combine(
          List.Transform(
              List.Zip({[Data][Document Code],[Data][Reference]}),
              each _{0} &" : "& _{1}),
          " - "))
in
#"Doc+Ref"

Stéphane

[SteveDesmedt]

Both solutions works like a charm !

Thanks a lot

[smpa01 · Accepted solution]

@SteveDesmedt  A  dax solution

Measure3 = 
VAR one =
    ADDCOLUMNS (
        src,
        "test",
            VAR exp1 =
                CALCULATE ( MAX ( src[Document Code] ) ) & ":"
                    & CALCULATE (
                        CONCATENATEX (
                            FILTER (
                                ALL ( src ),
                                src[Entry No.] = MAX ( src[Entry No.] )
                                    && src[Document Code] = MAX ( src[Document Code] )
                            ),
                            src[Document Reference],
                            " & ",
                            src[Document Reference]
                        )
                    )
            RETURN
                exp1
    )
VAR two =
    ADDCOLUMNS (
        one,
        "test2",
            CONCATENATEX (
                SUMMARIZE ( FILTER ( one, [Entry No.] = EARLIER ( [Entry No.] ) ), [test] ),
                [test],
                "||",
                [test], ASC
            )
    )
RETURN
    MAXX ( two, [test2] )