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 Code Document Reference 1 B00 12345677 2 B00 AER445ER 3 C00 IUYYN/1225 1 C00 DEEFFD25 ...
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] )

     

     

     

     

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

  • SteveDesmedt's avatar
    SteveDesmedt
    Frequent Visitor
    2 years ago

    Both solutions works like a charm !

    Thanks a lot