March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Solved! Go to 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] )
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
@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] )
User | Count |
---|---|
63 | |
55 | |
27 | |
16 | |
10 |