Excel BI Power Query Challenge Workout 077

Workouts Excel BI Challenge
12

LinkedIn Post by:
Victor Wang
 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split1 = Table.FromValue(Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z", " "})(Text.Combine(Source[String]))),
Split2 = Table.SplitColumn(Split1, "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
Transform = Table.TransformColumns(Split2, {{"Letters", Text.Trim}, {"Quantity", Number.From}}),
Result = Table.Sort([Table.Group](http://table.group/)(Transform, {"Letters"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}}), "Letters")
in
Result
13

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByCharacterTransition(
{"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([String])),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
 #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
 #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Custom.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Custom.2", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type1", {"Letters"}, {{"Quantity", each List.Sum([Custom.2]), type nullable text}}),
 #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letters", Order.Ascending}})
in
 #"Sorted Rows"
14

LinkedIn Post by:
Zoran Milokanović

Generating the result table w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
ConvertToTable = Table.FromRows(List.Split(List.Accumulate(
Text.ToList(Text.Remove(Text.Combine(Source[String]), " ")), {}, (s, c) => if Value.FromText(List.Last(s)) is number and Value.FromText(c) is number then List.RemoveLastN(s, 1) & {List.Last(s) & c} else s & {c}
), 2), {"Letters", "Quantity"}),
Solution = Table.Sort([Table.Group](http://table.group/)(ConvertToTable, {"Letters"}, {{"Quantity", each List.Sum(List.Transform([Quantity], Number.From))}}), "Letters")
in
Solution
15

LinkedIn Post by:
Krzysztof Kominiak
 

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 AddPos = Table.AddColumn(Source, "Pos", each List.Reverse(List.Skip(List.PositionOfAny(Text.ToList([String]), {"A".."Z"} ,2)))),
 SelCol = Table.AddColumn(AddPos, "Letters", each List.Accumulate( [Pos], [String], (s,c)=> Text.Insert(s, c, "||")))[[Letters]],
 SplitCol = Table.ExpandListColumn(Table.TransformColumns(SelCol, {{"Letters", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Letters"),
 RemSpaces = Table.ReplaceValue(SplitCol," ","",Replacer.ReplaceText,{"Letters"}),
 AddNumb = Table.AddColumn(RemSpaces, "Numb", each Number.From(Text.Select([Letters], {"0".."9"} ))),
 SelLetters = Table.TransformColumns(AddNumb, {"Letters", each Text.Remove(_, {"0".."9"} )} ),
 Result = Table.Sort(Table.Group(SelLetters, {"Letters"}, {{"Quantity", each List.Sum([Numb]),type number}}), {"Letters"} )
in
 Result
16

LinkedIn post by:
JvdV

=LET(x,DROP(REDUCE(0,A2:A5,LAMBDA(a,b,VSTACK(a,LET(c,TEXTSPLIT(b,,CHAR(ROW(65:90)),1),HSTACK(TRIM(TEXTSPLIT(b,,c,1)),--c))))),1),y,TAKE(x,,1),REDUCE({"Letters","Quantity"},SORT(UNIQUE(y)),LAMBDA(e,f,VSTACK(e,HSTACK(f,SUM(IF(y=f,DROP(x,,1))))))))

17

LinkedIn Post by:
Caroline Blake

=LET(_a,CONCAT(A2:A5),_b,TEXTSPLIT(_a," ",{1,2,3,4,5,6,7,8,9},TRUE),_c,TEXTSPLIT(_a,,_b,TRUE),_d,UNIQUE(_b),_e,MAP(_d,LAMBDA(_d,SUM(_c*(_d=_b)))),VSTACK(HSTACK("Letters","Quantity"),SORT(HSTACK(_d,_e),,1)))

18

LinkedIn Post by:
Sunny Baggu

2# 
=LET(
 _str, TEXTJOIN(" ", , A2:A5),
 _alpha, CHAR(SEQUENCE(26, , CODE("A"))),
 _modstr, REDUCE(_str, _alpha, LAMBDA(a, v, SUBSTITUTE(a, v, v & " "))),
 _tbl, SORT(WRAPROWS(TEXTSPLIT(_modstr, , " ", 1), 2)),
 _qty, MAP(
 UNIQUE(DROP(_tbl, , -1)),
 LAMBDA(a, SUM(--FILTER(DROP(_tbl, , 1), DROP(_tbl, , -1) = a)))
 ),
 HSTACK(UNIQUE(DROP(_tbl, , -1)), _qty)
)
19

LinkedIn post by:
Daniel Madhadha
 

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Split Column by Character Transition" = Table.SplitColumn(Source, "String", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"String.1", "String.2", "String.3", "String.4", "String.5"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Character Transition", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Value.1", "Value.2"}),
#"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Split Column by Character Transition1", {{"Value.2", type text}}, "en-US"),{{"Value.1", Text.Trim, type text}, {"Value.2", Text.Trim, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Value.2", type number}}),
#"Grouped Rows" = [Table.Group](http://table.group/)(#"Changed Type", {"Value.1"}, {{"Count", each List.Sum([Value.2]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value.1", Order.Ascending}})
in
#"Sorted Rows"
20

LinkedIn Post by:
Taeyong Shin
 

=LET(
 txt, REDUCE(A2:A5, SEQUENCE(10) - 1, LAMBDA(a,c, SUBSTITUTE(a, c & " ", c & ", ") )),
 s, SORT(TEXTSPLIT(ARRAYTOTEXT(txt), , ", ")),
 u, UNIQUE(LEFT(s)),
 SCAN( 0, HSTACK(u, u), LAMBDA(a,c,
 IF(ISNUMBER(a), c, SUM(IFERROR(NUMBERVALUE(s, " ", c), )) )
 ))
)
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)