Excel BI Power Query Challenge Workout 077

Workouts Excel BI Challenge
21

LinkedIn Post by:
Sunny Baggu

1# 
=LET(
 _str, TEXTJOIN(" ", , A2:A5),
 _letters, TRIM(TEXTSPLIT(_str, , SEQUENCE(10, , 0), 1)),
 _qty, TEXTSPLIT(_str, , CHAR(SEQUENCE(26, , CODE("A"))), 1) + 0,
 _uletters, SORT(UNIQUE(_letters)),
 HSTACK(_uletters, MAP(_uletters, LAMBDA(a, SUM(IF(_letters = a, _qty, 0)))))
)
22

LinkedIn Post by:
Kris Jaganah
 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Character Transition" = Table.SplitColumn(Source, "String", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"String - Copy.1", "String - Copy.2", "String - Copy.3", "String - Copy.4", "String - Copy.5"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Character Transition", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Letters_1", each Text.Trim([Value])),
#"Split Column by Position" = Table.SplitColumn(#"Added Custom", "Letters_1", Splitter.SplitTextByPositions({0, 1}, false), {"Letters", "Qty"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Qty", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Value"}),
#"Grouped Rows" = [Table.Group](http://table.group/)(#"Removed Columns1", {"Letters"}, {{"Quantity", each List.Sum([Qty]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letters", Order.Ascending}})
in
#"Sorted Rows"
23

LinkedIn Post by:
Anup Kumar

PQ - Menu Driven Solution 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 RemoveSpace = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"String"}),
 TransposedRows = Table.Transpose(RemoveSpace),
 MAllRows = Table.CombineColumns(TransposedRows,{"Column1", "Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"M"),
 UppercasedText = Table.TransformColumns(MAllRows,{{"M", Text.Upper, type text}}),
 SplitbyAlphabet = Table.SplitColumn(UppercasedText, "M", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"M.1", "M.2", "M.3", "M.4", "M.5", "M.6", "M.7", "M.8", "M.9", "M.10", "M.11", "M.12", "M.13", "M.14"}),
 Transposeback = Table.Transpose(SplitbyAlphabet),
 SplitbyDigit = Table.SplitColumn(Transposeback, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Column1.2"}),
 ChangedType = Table.TransformColumnTypes(SplitbyDigit,{{"Column1.2", Int64.Type}}),
 GroupedRows = Table.Group(ChangedType, {"Letters"}, {{"Quantity", each List.Sum([Column1.2]), type nullable number}}),
 SortedRows = Table.Sort(GroupedRows,{{"Letters", Order.Ascending}})
in
 SortedRows
24

LinkedIn Post by:
Kris Jaganah
 

=LET(a,CONCAT(A2:A5),b,TRIM(TEXTSPLIT(a,,SEQUENCE(10,,0),1)),c,UNIQUE(b),VSTACK({"Letters","Quantity"},SORT(HSTACK(c,MAP(c,LAMBDA(x,SUM((b=x)*TEXTSPLIT(a,,b,1))))))))
25

LinkedIn Post by:
Oscar Mendez Roca Farell

=LET(_t, CONCAT(A2:A5),_l, TEXTSPLIT(_t," ", SEQUENCE(10, ,1), 1),_n, TEXTSPLIT(_t, ,_l, 1), UNIQUE( ORDER( HSTACK(_l, ENCOL( MMULT( TOROW(_n)^0,(_l=TOROW(_l))*_n))))))

26

LinkedIn Post by:
Pieter de B

=LET(r,CONCAT(A2:A5),c,SUBSTITUTE(r," ",""),t,TEXTSPLIT(c,,SEQUENCE(10,,0),1),n,--TEXTSPLIT(c,,t,1),u,UNIQUE(t),m,MMULT(--(TOROW(t)=u),n),SORT(HSTACK(u,m)))

27

LinkedIn Post by:
Bo Rydobon
 

=LET(z,CONCAT(A2:A5),a,TEXTSPLIT(z," ",SEQUENCE(10,,0),1),u,UNIQUE(a),SORT(HSTACK(u,MAP(u,LAMBDA(v,SUM(TEXTSPLIT(z,,a,1)*(v=a)))))))
28

LinkedIn Post by:
محمد حلمي
 

=LET(r,CONCAT(A2:A5),v,TEXTSPLIT(r,,VSTACK(" ",ROW(1:10)-1),1),l,SORT(UNIQUE(v)),HSTACK(l,MAP(
l,LAMBDA(a,SUM((a=v)*TEXTSPLIT(r,,v,1))))))
29

Hi there,

With the help of Chatgpt I had to challenge this in excel. I learned alot here

image
image1212×506 35.3 KB

thanks for the Power Query for excel bi
Keith

30 
=LET(d,A2:A5,c,CONCAT(d),
l,(TEXTSPLIT(c,,SEQUENCE(10,,0))),
al,TRIM(FILTER(l,l<>"")),
q,TOCOL(--TEXTSPLIT(c,,CHAR(SEQUENCE(26,,65))),2),
u,UNIQUE(al),
tq,MAP(u,LAMBDA(x,SUM(FILTER(q,al=x)))),
r,VSTACK({"Letters","Quantity"},SORT(HSTACK(u,tq),1)),
r)

Quadri-PQ Challenge_77.xlsx (35.8 KB)

31

Power Query

let
	Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Record = Table.AddColumn (
		Source,
		"R",
		each [
			S = Splitter.SplitTextByCharacterTransition ( { "0" .. "9", " " }, { "A" .. "Z" } ) (
				[String]
			),
			T = List.Transform (
				S,
				( f ) =>  { Text.Select ( f, { "A" .. "Z" } ) }
					& { Number.From ( Text.Select ( f, { "0" .. "9" } ) ) }
			)
		][T]
	),
	Table  = Table.FromRows ( List.Combine ( Record[R] ), { "Letter", "Q" } ),
	Group  = Table.Group ( Table, "Letter", { "Quantity", each List.Sum ( [Q] ) } ),
	Return = Table.Sort ( Group, "Letter" )
in
	Return
``

PQ Challenge_77.xlsx (23.4 KB)