Excel BI Power Query Challenge Workout 001

Workouts Excel BI Challenge
1

– CAN YOU SOLVE THIS - POWER QUERY CHALLENGE 1 —
Extract the numeric digits from the given strings in a column and sum the digits for those extracted strings in another column.

Download Practice File - https://lnkd.in/dncWtT_v

#powerbi, #powerquery, #dax, #powerquerychallenge, #powerbichallenge

image
image1278×602 83.1 KB

Although this is a “Power Query Challenge” please feel free to submit an Excel solution instead.

2

I think I started doing these challenges about a week after they started in August 2022, so I’m going back and doing the ones I didn’t do the first time through…

Here’s my solution to this one:

image
image1043×566 28.8 KB

Click for M Code

let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
ExtractDigits = Table.AddColumn(Source, “Digits”, each Text.Select( Text.From( [String] ), {“0”…“9”})),
SumDigits = Table.AddColumn(ExtractDigits, “Sum of Digits”, each [
a = Text.ToList( [Digits] ),
b = List.Transform( a, Number.From),
c = List.Sum( b )
][c])
in
SumDigits

3

Skipping the intermediate Digits column and using List.Accumulate, use this as the custom column formula:

List.Accumulate(
    Text.ToList(Text.From([String])),
    null,
    (sum, x) =>
        if List.Contains({"0".."9"}, x)
        then sum ?? 0 + Number.From(x)
        else sum
)
2 Likes
4

@AlexisOlson ,

Nice solution! I rarely use List.Accumulate, generally finding List.Generate easier to keep straight and a bit more intuitive.

  • Brian
1 Like
5

Yeah, List.Generate is more powerful and intuitive. I typically only List.Accumulate for simple accumulations rather than more advanced recursive logic.

2 Likes
6

Here is one way to do it in Power Query.

let
	Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Digits = Table.AddColumn (
		Source,
		"Digits",
		each [
			Digits = Text.Select ( Text.From ( [String] ), { "0" .. "9" } ),
			Tolist = Text.ToList ( Digits ),
			Sum    = List.Sum ( List.Transform ( Tolist, Number.From ) )
		][[Digits], [Sum]]
	),
	Return = Table.ExpandRecordColumn ( Digits, "Digits", { "Digits", "Sum" } )
in
	Return

PQ_Challenge_1_Problem.xlsx (26.8 KB)

7

Here is one way to do it in Excel.

=REDUCE(
    {"Digits", "Sum"},
    B3:B11,
    LAMBDA(a, b,
        LET(
            \sq, SEQUENCE(LEN(b)),
            \s, MID(b, \sq, 1),
            \n, FILTER(\s, ISNUMBER(--\s), ""),
            \c, CONCAT(\n),
            \t, IFERROR(SUM(--\n), ""),
            \r, VSTACK(a, HSTACK(\c, \t)),
            \r
        )
    )
)

Here:
sq = Sequence
s = Split
n = Filtered Numbers
c = Concatenated
t = Total
r = Return

PQ_Challenge_1_Problem.xlsx (21.1 KB)

8

Excel Formula Approach

=LET(str,B3:B11,
digits,MAP(str,LAMBDA(x,IFERROR(CONCAT(TOCOL(--MID(x,SEQUENCE(LEN(x)),1),3)),""))),
sum_of_digits,MAP(str,LAMBDA(x,IFERROR(SUM(TOCOL(--MID(x,SEQUENCE(LEN(x)),1),3)),""))),
output,HSTACK(str,digits,sum_of_digits),
headers,{"String","Digits","Sum of Digits"},VSTACK(headers,output))

Quadri Atharu PQ_Challenge_1_Solution.xlsx (43.1 KB)