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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SteveDesmedt
Frequent Visitor

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

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

 

 

smpa01_0-1702401919345.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
slorin
Super User
Super User

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

Both solutions works like a charm !

Thanks a lot

smpa01
Super User
Super User

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

 

 

smpa01_0-1702401919345.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.