Forum Discussion

SteveDesmedt's avatar
SteveDesmedt
Frequent Visitor
2 years ago
Solved

Convert Rows to column and group in new column

Hi all, 

 

I'm struggling with following scenario :

 

Table1

Entry No.Document CodeDocument Reference
1B0012345677
2B00AER445ER
3C00IUYYN/1225
1C00DEEFFD25
1B004545878787
3C00FFROIIS/445
3B0044558877

 

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 NoDocument + Reference
1B00 : 12345677 & 4545878787 - C00 : DEEFFD25
2B00 : AER445ER
3B00 : 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

Need Help
  • smpa01's avatar
    smpa01
    2 years ago

    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] )

     

     

     

     

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

    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] )

     

     

     

     

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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