-
Notifications
You must be signed in to change notification settings - Fork 0
/
LPQ.pq
1284 lines (1238 loc) · 60.5 KB
/
LPQ.pq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/**
* Inspired by Power-BI-Desktop-Query-Extensions - https://github.com/tnclark8012/Power-BI-Desktop-Query-Extensions
* Expression.Evaluate(Text.FromBinary(Web.Contents("https://gist.github.com/laktiv/700a6d65e098189881ecd77e585b233a")),#shared)
**/
let
MissingTests = List.Select(Record.FieldNames(_extensionLibrary), each not
List.MatchesAny(Tests, (testRecord as record) => Text.Contains(testRecord[CaseName], _))),
MissingDocumentation = List.Select(Record.FieldNames(_extensionLibrary), each not
Record.HasFields(Value.Metadata(Value.Type(Record.Field(_extensionLibrary, _))), "Documentation.Examples")),
Tests = {
[ CaseName = "List.From",
Test = (library as record) =>
TestUtils[AssertEqual](
{"A", "B", "C"},
library[List.From]("{A, B, C}"),
"Text should be split on ,") ],
[ CaseName = "List.From - no braces",
Test = (library as record) =>
TestUtils[AssertEqual](
{"A", "B", "C"},
library[List.From]("A, B, C"),
"Text should be split on ,") ],
[ CaseName = "List.Flatten",
Test = (library as record) =>
TestUtils[AssertEqual](
{ 1, 2, Table.FromRecords({[x=1]}), 3, 4, 5},
library[List.Flatten]({ 1, 2, Table.FromRecords({[x=1]}), {3, 4, 5} }),
"Flattened list") ],
[ CaseName = "Number.ToLetters",
Test = (library as record) =>
TestUtils[AssertEqual](
{ "A", "BC", "BAD" },
{ library[Number.ToLetters](1),
library[Number.ToLetters](55),
library[Number.ToLetters](1382) },
"1, 2, and 3 letter translations") ],
[ CaseName = "Table.RenameColumn",
Test = (library as record) =>
TestUtils[AssertEqual](
{"NewName", "NumberCol"},
Table.ColumnNames(library[Table.RenameColumn](TestUtils[SimpleTable], "TextCol", "NewName")),
"Column should have new name") ],
[ CaseName = "Text.Until",
Test = (library as record) =>
TestUtils[AssertEqual](
"abc ",
library[Text.Until]("abc 123", "1"),
"Proper substring should be found") ],
[ CaseName = "Text.Substring - infer end",
Test = (library as record) =>
TestUtils[AssertEqual](
"BI Rules!",
library[Text.Substring]("Power BI Rules!", 6),
"Text.Substring('Power BI Rules', 6)") ],
[ CaseName = "Switch case pairs",
Test = (library as record) =>
TestUtils[AssertEqual](
2,
library[Switch]("B", {{"A", 1}, {"B", 2}, {"C", 3}}),
"B is second case") ],
[ CaseName = "Switch separate case and result",
Test = (library as record) =>
TestUtils[AssertEqual](
4,
library[Switch]("TE", {"QB", "RB", "WR", "TE", "K", "D/ST"}, {1, 2, 3, 4, 5, 6}),
"TE is 4th case") ] },
TestUtils = [
AssertEqual = (expected as any, actual as any, description as text) =>
let
expectedType = Value.Type(expected),
actualType = Value.Type(actual),
listAsText = (list as list) =>
"{" & Text.Range(List.Accumulate(expected, "", (state, current) => state & ", " & Text.From(current)), 1) & " }",
expectedAsText = if expected is list then listAsText(expected) else Text.From(expected),
actualAsText = if expected is list then listAsText(actual) else Text.From(actual),
typeAsText = (value as any) =>
if value is binary then "binary" else
if value is date then "date" else
if value is datetime then "datetime" else
if value is datetimezone then "datetimezone" else
if value is duration then "duration" else
if value is function then "function" else
if value is list then "list" else
if value is logical then "logical" else
if value is none then "none" else
if value is null then "null" else
if value is number then "number" else
if value is record then "record" else
if value is table then "table" else
if value is text then "text" else
if value is time then "time" else
if value is type then "type" else
if value is any then "any"
else "unknown -- not a primitive type!"
in
if not Value.Is(actualType, Value.Type(expectedType)) then error "Expected type " & typeAsText(expectedType) & " does not match actual type " & typeAsText(actualType)
else if expected = actual then true
else if expectedAsText = actualAsText then error "Cannot provide accurate failure message! Text versions of expected and actual are identical, but they failed the equality test!"
else error "Expected: " & expectedAsText & "; Actual: " & actualAsText & "; Reason: " & description,
SimpleTable = Table.FromRecords({[TextCol = "A", NumberCol = "1"], [TextCol = "B", NumberCol = 2], [TextCol = "C", NumberCol = 3]}) ],
TestResults =
let failedTests = List.Select(
List.Transform(Tests, (suite as record) =>
let
testResult = try suite[Test](_extensionLibrary)
in
if testResult[HasError] then Error.Record(suite[CaseName], testResult[Error][Message], null) else true), each _ <> true)
in if List.IsEmpty(failedTests) then "All " & Text.From(List.Count(Tests)) & " tests passed! :)" else failedTests,
_extensionLibrary = [
// # DEPENDENCIES
Document = (name as text, description as text, category as text, valueOrExample as any, optional valueIfExample as any) =>
let value = if valueIfExample is null then valueOrExample else valueIfExample,
examples = if valueIfExample is null then {} else valueOrExample,
category = if category is null then {} else category
in Value.ReplaceType(value, Value.Type(value) meta [
// [Description="", Code="", Result=""]
Documentation.Name = name,
Documentation.Description = description,
Documentation.Category = category,
Documentation.Examples = examples
]),
Switch = Document(
"Switch",
"Given a value, find it's paired item <br>"&
"Switch(value as any, cases as list, results as list, optional default as any) <br>"&
"Switch(value as any, pairs as list, optional default as any)",
"Development", {
[ Description = "Using separate lists",
Code="LPQ[Switch](1, {1, 2, 3}, {""A"", ""B"", ""C""})",
Result="A"],
[ Description = "Using one paired list",
Code="LPQ[Switch](1, {{1, ""A""}, {2, ""B""}, {3, ""C""}})",
Result="A"]},
(value as any, casesOrPairs as list, optional resultsOrDefault as any, optional default as any) =>
let hasPairs = List.First(casesOrPairs) is list,
usingPairs =
let targetPosition = List.PositionOf(casesOrPairs, value, Occurrence.First, (case, theValue) => theValue = case{0})
in if targetPosition = -1 then resultsOrDefault else casesOrPairs{targetPosition}{1},
usingCases =
let cases = casesOrPairs,
results = resultsOrDefault
in if List.IsEmpty(cases) or List.IsEmpty(results) then default else if value = List.First(cases) then List.First(results) else @Switch(value, List.Skip(cases, 1), List.Skip(results, 1), default)
in if hasPairs then usingPairs else usingCases)
,
// ## BULK REPLACE & TABLES
MyFindReplace =
let table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcpBDoIwEIXhq5iuJMGF8QRQSpxYW0LHuCAsEFk0Ykuwxus7Lbs33/xdxxBYztCyPu8YOOdPFd2glKYRDX/WxeIOamv4GaQksduIZr7LtBKZWyPaJNy/l2EMZFxfm4Jj0nIexhdZKQt+SVKv/hMeNkykdasNloAiffbHg5l92ImnDda7jIJIUuNOVICgVcb6/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t])
in Table.ReplaceValue(table,"",null,Replacer.ReplaceValue,{"Replace"}),
MyFindReplace_Sample =
let table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LDoIwFAV/hXQrxijInrY8GnkFKi4ICyQsGhUIYvh925JCdzOTe5NTVQCXBJgA1GYFQhxvXAauQpwpMg7GFnFuaWxrfNXYUezYkM1KMkSOnpLldA7gLhddLF3sXSjjRInkoPOHqe14CDw/zZEnK+n7wcI8kiRJOcivhfXi70GS9QaFJIp4YSuIVvzGbuKpuGdeLgsaPmPTiu0ojTMXUVnhu2lfvMHIRTdZ/Gn4zk82iyV+nhYUEsq31H8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t])
in Table.ReplaceValue(table,"",null,Replacer.ReplaceValue,{"Replace"}),
MyFindReplace_InnoSpecs =
let table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/PboJAEIdfZcKpjcQo9gVaOOiBlMSaHoiHLYxKxJ3NsqTi03f/AAumt5nv983Mbp4H8SF5h5gkNkFom+AY5sEHUaMgrqm4wku6fbzqcGBXZ7AGnTDmjrh43wpR4w25YrKDjH5R6iucY6FImlNPyA6leCNt261aGdt+pWK8ZLKEgRM/VWfv7asHTtfsuEJ5YgV65asTM8X08F2V6uIdPzY7mlSNqFk3/8Rnq0SrrLhN0h1Ey7Wmppyy1c8zZFPQb85IKlgv30yWZP8lPtjcQVemhfvGkmhCovFOCM7oTVjA7BkujoaxMT7+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t])
in Table.ReplaceValue(table,"",null,Replacer.ReplaceValue,{"Replace"}),
BulkTableReplace =
let BulkReplace = (
DataTable as table,
FindReplaceTable as table,
DataTableColumn as list,
optional ReplaceFullCell as logical
) =>
/*
* Replace values in column by table list of replaces
* Create extra table "MyFindReplace" with [Find] and [Replace] columns
*/
let
FindReplaceList = Table.ToRows(FindReplaceTable), //Convert the FindReplaceTable to a list using the Table.ToRows function so we can reference the list with an index number
Counter = Table.RowCount(FindReplaceTable), //Count number of rows in the FindReplaceTable to determine how many iterations are needed
BulkReplaceValues = (DataTableTemp, n) => //Define a function to iterate over our list with the Table.ReplaceValue function
let
ReplaceType = if ReplaceFullCell=null or ReplaceFullCell=false // set Replace type
then Replacer.ReplaceText
else Replacer.ReplaceValue,
ReplaceTable = Table.ReplaceValue( //Replace values using nth item in FindReplaceList
DataTableTemp,
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0}, //replace null with empty string
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
ReplaceType,
DataTableColumn
)
in
if n = Counter - 1 //if we are not at the end of the FindReplaceList then iterate through Table.ReplaceValue again
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n+1),
Output = BulkReplaceValues(DataTable, 0) //Evaluate the sub-function at the first row
in
Output
in
BulkReplace
,
// # DATE
// ## Inno
Date.DateFromISOwn = Document(
"Date.DateFromISOwn",
"Compute date of the first day of the week given an ISO weeknumber. If the Year is not entered, it will default to 'this' year.",
"Dates.Convertasion",
{[Description="Year must be a full year: eg 2021 vs 21. The latter will not be interpreted as the year 2021.",
Code="LPQ[Date.DateFromISOwn](""week number"", opt. ""year"")",
Result="Date on start of week #" ]},
(wn as number, optional year as number) as date =>
let
yr = if year = null then Date.Year(DateTime.LocalNow()) else year,
wn1Start = Date.StartOfWeek(#date(yr,1,1), Day.Monday),
w1 = if Date.AddDays(wn1Start,3) < #date(yr,1,1) then Date.AddDays(wn1Start,7) else wn1Start
//-2 if not 01.01.01 start of the year(?)
in Date.AddDays(w1, 7*(wn-1)) ),
DateTime.FromUnix = Document(
"DateTime.FromUnix",
"Converts UNIX timestamp to DateTime.",
"Date.Transformation",
{[Description="Converts UNIX timestamp to DateTime.",
Code="LPQ[DateTime.FromUnix](""1710291332078"")",
Result="2024-03-13T00:55:32.0780000" ]},
(ts) => #datetime(1970,1,1,0,0,0) + #duration(0,0,0,Number.FromText(ts)/1000) ),
// ## Basic calendar
Date.Calendar = Document(
"Date.Calendar",
"Generate a calendar table for a given date span - can be text or proper dates. Current columns are Date, DayOfWeek, Month, MonthNum, WeekStartData, WeekStart, Year, YearMonth",
"Dates.Date",
{[Description="2024 calendar",
Code="LPQ[Date.Calendar](""1/1/2024"", ""12/31/2024""",
Result="2024 calendar" ]},
(start as any, end as any) =>
let
StartDate = Date.From(start),
EndDate = Date.From(end),
Source = Date.DatesBetween(StartDate, EndDate),
FromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Date = Table.RenameColumns(FromList,{{"Column1", "Date"}}),
DayOfWeek = Table.AddColumn(Date, "Day of Week", each Date.DayName([Date])),
Month = Table.AddColumn(DayOfWeek, "Month", each Date.MonthName([Date])),
MonthNum = Table.AddColumn(Month, "MonthNumber", each Date.Month([Date])),
WeekStartDate = Table.AddColumn(MonthNum, "WeekStartDate", each Date.StartOfWeek([Date])),
WeekStart = Table.AddColumn(WeekStartDate, "Week Start", each [Month] & " " & Text.From(Date.Day([WeekStartDate]))),
Year = Table.AddColumn(WeekStart, "Year", each Date.Year([Date])),
YearMonth = Table.AddColumn(Year, "YearMonth", each Number.From(Text.From([Year]) & (if [MonthNumber] < 10 then "0" else "") & Text.From([MonthNumber]))),
Result = YearMonth
in Result),
Date.DatesBetween = Document(
"Date.DatesBetween",
"Returns a list of dates in a given span (inclusive). Start and end parameters can be any order",
"Dates.Date",
{[Description="Date range",
Code="LPQ[Date.DatesBetween](""1/1/2016"", ""1/3/2016"")",
Result="{""1/1/2016"", ""1/2/2016"", ""1/3/2016""}" ]},
(start as any, end as any) =>
let
StartDate = Date.From(start),
EndDate = Date.From(end),
adjustedStart = List.Min({StartDate, EndDate}),
adjustedEnd = List.Max({StartDate, EndDate}),
GetDates = (start as date, end as date, dates as list)=> if start > end then dates else @GetDates(Date.AddDays(start, 1), end, List.Combine({dates, {start}})),
Dates = GetDates(adjustedStart, adjustedEnd, {})
in
Dates),
Date.DayName = Document(
"Date.DayName",
"Returns the English day of the week name for a date",
"Dates.Date",
{[Description="Get the day name",
Code="LPQ[Date.DayName](""9/9/2016"")",
Result="Friday" ]},
(date as any) =>
let Eng = {"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},
Ukr = {"Неділя","Понеділок","Вівторок","Середа","Четвер","Пятниця","Субота"},
Lang = Eng
in Switch(Date.DayOfWeek(DateTime.From(date)), {0,1,2,3,4,5,6}, Lang, null)),
Date.MonthName = Document(
"Date.MonthName",
"Returns the English month name for a date",
"Dates.Date",
{[Description="Get the month name",
Code="LPQ[Date.MonthName](""9/9/2016"")",
Result="September" ]},
(date as any, optional lang as text) =>
let eng = {"January","February","March","April","May","June","July","August","September","October","November","December"},
ukr = {"Січень","Лютий","Березень","Квітень","Травень","Червень","Липень","Серпень","Вересень","Жовтень","Листопад","Грудень"},
Lang = if lang is null or lang="" then eng else ukr,
monthNumber = if date is number then date else Date.Month(DateTime.From(date))
in Switch(monthNumber, {1,2,3,4,5,6,7,8,9,10,11,12}, Lang, null))
,
// # LIST
// ## Compare
List.CompareListsCheck = (ListSource as list, ListCompare as list, optional CaseIgnore as logical) =>
//fxList = (l) => try Text.Combine( List.Transform(List.Distinct(List.Transform(l, each Text.Split(_, " "){1})), each Text.From(_)), "," ) otherwise "",
if (CaseIgnore ?? {CaseIgnore=""}{1}?) <> null
then List.ContainsAny(ListSource, ListCompare, (x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase))
else List.ContainsAny(ListSource, ListCompare, (x, y) => x=y),
List.CompareListsMatches = (ListSource as list, ListCompare as list, optional CaseIgnore as logical) =>
//fxList = (l) => try Text.Combine( List.Transform(List.Distinct(List.Transform(l, each Text.Split(_, " "){1})), each Text.From(_)), "," ) otherwise "",
if (CaseIgnore ?? {CaseIgnore=""}{1}?) <> null
then List.Select(ListSource, each List.Contains(ListCompare, _, (x,y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)))
else List.Select(ListSource, each List.Contains(ListCompare, _, (x,y) => Text.Contains(x, y, Comparer.Equals))),
// ## Transform
List.Flatten = Document(
"List.Flatten",
"Recursively flattens list elements. The end result is a single list",
"Lists.Transformation",
{[Description="Flattening nested lists into one",
Code="LPQ[List.Flatten]({ 1, 2, Table.FromRecords({[x=1]}), {3, 4, 5} })",
Result="{ 1, 2, Table.FromRecords({[x=1]}), 3, 4, 5}" ]},
(list as list) => List.Accumulate(list, {}, (state, current) =>
let
currentListContent = if current is list then @List.Flatten(current) else {current}
in
List.Combine({state, currentListContent}))),
List.FlattenAdvanced = Document(
"List.FlattenAdvanced",
"Recursively flattens list elements. The end result is a single list",
"Lists.Transformation",
{[Description="Flatten a nested list with mixed levels into a simple list.",
Code="LPQ[List.Flatten]({1, {2, 3}, {1, {2, 3}}})",
Result="{1, 2, 3, 1, 2, 3}" ]},
(L as list) as list =>
let
IsListType = (x) as logical => Value.Type(x) = List.Type,
FlattenOneLevel = (L as list) as list =>
List.Combine(
List.Transform(L, each if IsListType(_) then _ else {_})
),
FlattenAll = (L as list) as list =>
if List.MatchesAny(L, IsListType) // Check if L is nested
then @FlattenAll(FlattenOneLevel(L))
else L
in
FlattenAll(L)),
List.From = Document(
"List.From",
"Converts a text representation of a list into a list of the elements. Items are considered to be split by ,",
"Lists.Transformation",
{[Description="Convert a text list",
Code="LPQ[List.From](""{A, B, C}"")",
Result="{ ""A"", ""B"", ""C"" }" ]},
(simpleTextList as text) =>
let
trimWhitespace = Text.Trim(simpleTextList),
listToSplit = Text.TrimEnd(Text.TrimStart(trimWhitespace, "{"), "}"),
Result = List.Transform(Text.Split(listToSplit, ","), each Text.Trim(_))
in
Result),
List.ToText = Document(
"List.ToText",
"Converts a list to a textual representation. Inverse of List.From",
"Lists.Transformation",
{[Description="Conver to text",
Code="LPQ[List.ToText]({ 1, 2, 3})",
Result="""{1, 2, 3}""" ]},
(list as list) => List.Accumulate(list, "{", (state, current) => current & Text.From(current)) & "}"),
List.Swap = (_,_from as number ,_to as number) =>
let
from = List.Min({_from,_to}),
to = List.Max({_from,_to})
in if from=to then _ else
List.Range(_,0,from)
&{_{to}}
&List.Range(_,from+1,to-from-1)
&{_{from}}
&List.Range(_,to+1),
List.Shuffle = (n) => List.Accumulate({0..(n-2)},{0..n},(_,iterator)=>
List.Swap(List.Buffer(_),
Number.Round(Number.RandomBetween(iterator,n)),
iterator
)),
List.ToRecord = Document(
"List.ToRecord",
"Transform a list of strings to a record using a given lambda (passed values k). The built-in Record.FromList only takes static lists...",
"List.ToRecord",
{[Description="Transform a list of strings to a record using a given lambda (passed values k)",
Code="LPQ[List.ToRecord]({""a"",""b""}, (k) => Text.Upper(k)))",
Result="[a=""A"", b=""B""]" ]},
(List as list, Lambda as function) as record =>
let
Transformed = List.Transform(List, Lambda) //each Lambda(_)
in
Record.FromList(Transformed, List))
,
// # NUMBERS
Number.Digits = {0,1,2,3,4,5,6,7,8,9},
Number.ParseText = Document(
"Number.ParseText",
"Returns the first number (1+ consecutive digits) in the given text. Optionally, provide allowed characters to ignore them",
"Numbers.Transformation",
{[Description="Allow commas",
Code="LPQ[Number.ParseText](""It's over 9,000!"", 0, {"",""})",
Result="9000" ]},
(text as text, optional startIndex as number, optional allowCharacters as list) =>
let
consider = if startIndex is null then text else Text.Range(text,startIndex),
_allowCharacters = if allowCharacters is null then {} else allowCharacters,
numberSeries = List.FirstN(List.Skip(Text.ToList(consider), each not Text.IsNumber(_)), each Text.IsNumber(_) or List.Contains(_allowCharacters, _))
in
if text is null then null else Text.FromList(numberSeries)),
Number.ToLetters = Document(
"Number.ToLetters",
"Converts a number (starting at 1) to an alphabet representation. Works like column headers in Excel.",
"Numbers.Transformation",
{[Description="Column 27",
Code="LPQ[Number.ToLetters](27)",
Result="AB" ]},
(value as number) =>
let
GetLetter = (num as number) =>
let
number = Number.Mod(num, 26),
val = if number = 0 then 26 else number,
valid = number < 26 and number > 0
in
if valid then Text.At(Text.Alphabet, val - 1) else error "Can't get letter for " & Text.From(num),
func = (value as number, factor as number) =>
let
ThisLetter = GetLetter(Number.RoundDown(value/Number.Power(26, factor))),
Result = if value <= Number.Power(26, factor) then "" else @func(value, factor+1) & ThisLetter
in
Result
in
if value <= 26 then GetLetter(value) else func(value, 1) & GetLetter(value)),
Number.ProperNumber = Document(
"Number.ProperNumber",
"Concerts text as number to proper number type. Also will erase inconvinient thouthands and decimal symbols to regular format. <br>" &
"In case of error will return input text",
"Numbers.Transformation",
{[Description="Allow commas",
Code="LPQ[Number.ProperNumber](""It's over 9,000!"", null, {"",""}, 0)",
Result="9000" ]},
(text as text, optional digitsAfter as number, optional allowCharacters as list, optional startIndex as number) =>
let
after = if digitsAfter is null then 0 else digitsAfter,
consider = if startIndex is null then text else Text.Range(text,startIndex),
_allowCharacters = if allowCharacters is null then {} else allowCharacters,
numberSeries = List.FirstN(List.Skip(Text.ToList(consider), each not Text.IsNumber(_)), each Text.IsNumber(_) or List.Contains(_allowCharacters, _))
in
if text is null then null else Number.Round(Text.FromList(numberSeries), after))
,
// # SPLITTERS
Splitter.SplitTextByNonAlpha = Document(
"Splitter.SplitTextByNonAlpha",
"Splits text by characters that aren't [A-Za-zА-Яа-я]",
"Splitter.Transformation",
{[Description="Split text",
Code="LPQ[Splitter.SplitTextByNonAlpha](""A1B,C"")",
Result="{ ""A"", ""B"", ""C"" }" ]},
(line as text) => Splitter.SplitTextByNotIn(Text.Alphabet)),
Splitter.SplitTextByNotIn = Document(
"Splitter.SplitTextByNotIn",
"Splits text on any characters that aren't the provided 'safe' characters",
"Splitter.Transformation",
{[Description="Split on non-alphanumeric",
Code="LPQ[Splitter.SplitTextByNotIn](LPQ[Text.AlphaNumeric])(""Power BI is #1"")",
Result="{""Power BI is "", ""1""}" ]},
(safeCharacters as text) => (line as nullable text) =>
if line is null then
{}
else
List.Accumulate(Text.ToList(line), {null} , (state, current) =>
let
doSkip = not Text.Contains(safeCharacters, current),
lastItem = List.Last(state),
appendLast = lastItem<>null
in
if doSkip then
if lastItem is null then
state
else
List.Combine({state, {null}})
else if appendLast then
List.Combine({List.RemoveLastN(state, 1), {lastItem & current}})
else
List.Combine({List.RemoveLastN(state, 1), {current}})))
,
// # TABLE
Table.AddBlankRow = (table as table) => Table.Combine({table, Table.FromRecords({[]})}),
Table.DrillIntoColumn = (table as table, columnName as text) =>
let FindValue = (value as any) =>
if value is list then
if List.Count(value) = 1 then @FindValue(List.First(value))
else if List.Count(value) = 0 then null
else error "Couldn't find single value"
else if value is table then
if Table.RowCount(value) = 1 then @FindValue(List.First(Table.ToColumns(value)))
else if Table.RowCount(value) = 0 then null
else error "Couldn't find single value"
else value,
Result = Table.TransformColumns(table, {{columnName, FindValue}})
in Result,
Table.EnterDataFormula = Document(
"Table.EnterDataFormula",
"Convert a table into a formula that's compatible with the Enter Data UI. This function returns an expression you can copy and paste to editthe table using Enter Data. Paste the formula, then click the gear icon next to the step name. This is an easy way to allow people to customize a table you've queried",
"Table.Convertion",
{[Description="Simple table",
Code="Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/List_of_Super_Bowl_champions"")){3}[Data]",
Result="Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""jVVtb9s2EP4rRD4aRqI3S9TH2Gm7ZWkb1MGGom6Aq83IhBkyoOik2a8fT6Ski6Fu+yDdWea9Pffc8du3M342P7uVzrU/jrbZs7UTQgnb+q+lfzL/nFeLxIu0ror55pglWcK8viA6J3o96vVwJksSqvNRT5Ognn2fh1yuQClo2cq8/DCvmMbCPzmmUWaLkEYyn2GkNIhFEFUQvBN1FkQexGI2uP8kXtg73SjQO3YLzkrjMEgRn/NFEmrlpL66JLmnRM+JXhC9IvWR82lxWqvQz8KypTV6a9pYJxZ5nlex1mpMg5dEp98p5OR7TWEmqaYLkgY2eQ2avbegt7LdGlbUof1dApgLz/MASQCcF0EEpH3wDuJiNkfXWUAabT9YITRbwqvHeXsIPovBZ4S5jDWl3kdZjbqHfBbKmXUkGd1iA78ae2AfJGg36ZRH46QzTqKIrtLR1Wc4IA82FzemZZe68cRv2ReQu5Bs3vO/7JOt5kixEt886d45Vp1kg8u/oN1L3Tij2Rexaw9ST3qqAj95FIGmPFC4Jhl+lPAo2ZVRT/vgKeuHoeg9pWT2MqLnhCDkO6ccROSWoJx8NFZsLn7XO48pPBklcQCV6yNmdDJKPrbJj+LIejol9UmUj1Jr0RoH7E/pQWnQc9LPXNJ7ronngEkVyFaVszHh48MDKMOWUqlJN3VCZoDAUxMY6pwkiIASCmwu1u6c3ZijRDY8tpFeHQh5nIUq0r4O3Iotw//WApxTgnm5h5fDhHW3DGdhKDtRBOuMdsNHfhZDy7vCYoEdo8dhzkghePQP0C3uT+le2WovxUOfQfqmieX8Hkeuvh8M/eEtNB5XAXbShoesk3JM93YvFewE0hPYO2g8ejHbjHaExxEkha6kXzdag5M+oG5ATRuS9nF+UukKrGeqBr9ftNuHmT3xgDs6LKZ4BaR0g1wL1xdKAUaK3w+n7+DxCbo1tjxut6CFsFNGuAXehvhslfC9YGuQejIOjslogkv4SgrfgNUebDOWk77hNh1f/OfS+RXmx+o9qK3R0zZ8jHIncA5bIdiddPCL8/VpDCv/Nh5nD/jO460mrPBSD1Hw10qJZ9Fdsf5qexmi4IOuUxzXT5uLy8HgSjhrpGM30vyP09f+PmmNfvYLQLBraI5g/9voN3NscS3fiZ/wbzG+/wM="", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Appearances = _t, Team = _t, Wins = _t, Losses = _t, Winning percentage = _t, Season(s) = _t])" ]},
(Table as table) =>
let
Encoded = Table.JsonEncode(Table),
ColumnMeta= "[" & Text.Range(List.Accumulate(Table.ColumnNames(Table), "", (state, current) => state & ", " & current & " = _t"), 2) & "]",
Text =
"Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""" & Encoded & """, BinaryEncoding.Base64), Compression.Deflate))," &
" let _t = ((type text) meta [Serialized.Text = true]) in type table " & ColumnMeta & ")"
in
Text
),
// if fieldNames aren't specified, use the field names from the first row of the column.
// Perform a cross join of lists. Example usage:
// Table.FromListCrossJoin({ {ColorsTable[ColorName], "Color"}, {NumbersTable[Number], "Number"}})
// Will give me a new table with two columns, "Color" and "Number" which contains one row for each possible
// combination of colors and numbers
// Table.FromListCrossJoin({{"Red", "Blue"}, "Color"}, {{1,2,3}, "Number"}}) =
// Table.FromRecords({[Color="Red", Number=1],[Color="Red", Number = 2],[Color="Red", Number = 3],[Color="Blue", Number=1],[Color="Blue", Number=2],[Color="Blue", Number=3]})
Table.FromListCrossJoin = (listColumnNamePairs as any) =>
let remainingPairs = List.Skip(listColumnNamePairs, 1),
current = List.First(listColumnNamePairs),
theList = List.First(current),
columnName = List.First(List.Skip(current),1),
firstTable = Table.FromList(theList, null, {columnName}),
doStuff = (table as table, remainingPairs as list) =>
if List.Count(remainingPairs) <= 0 then table else
let
current = List.First(remainingPairs),
theList = List.First(current),
columnName = List.First(List.Skip(current), 1),
nextTable = Table.ExpandListColumn(Table.AddColumn(table, columnName, each theList), columnName)
in @doStuff(nextTable, List.Skip(remainingPairs, 1)),
Result = doStuff(firstTable, remainingPairs)
in Result,
Table.JsonDecode = (encoded as text) =>
let Decompressed = Binary.Decompress(Binary.FromText(encoded, BinaryEncoding.Base64), Compression.Deflate),
Decoded = Table.FromRows(Json.Document(Decompressed), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t])
in Decoded,
Table.JsonEncode = (table as table) =>
let Rows = Table.ToRows(table),
Json = Json.FromValue(Rows),
Compressed = Binary.Compress(Json, Compression.Deflate),
Encoded = Binary.ToText(Compressed, BinaryEncoding.Base64)
in Encoded,
// ## Replaces a value if it matches a predicate
Table.ReplaceValueIf = (table as table, replaceIf as function, after as any, columnNameOrList as any) =>
Table.ReplaceValue(table, null,after, (text, old, new)=>if replaceIf(text) then new else text, if columnNameOrList is list then columnNameOrList else {columnNameOrList}),
// ## Splits camelCased and PascalCased column names.
Table.SplitColumnNames = (table as table) => Table.RenameColumns(table, List.Transform(Table.ColumnNames(table), each {_, Text.SplitCamelCase(_)})),
// ## Splits camelCased and PascalCased text in a column.
Table.SplitColumnText = (table as table, columns as list) => if List.Count(columns) = 0 then table else Table.TransformColumns(@Table.SplitColumnText(table, List.Skip(columns, 1)), {{List.First(columns), Text.SplitCamelCase}}),
Table.TransformColumn = (table as table, column as text, transform as function) => Table.TransformColumns(table, {{column, transform}}),
Table.RenameAndTransformColumn = (table, currentName as text, newName as text, transform as function) => Table.TransformColumn(Table.RenameColumns(table, {currentName, newName}), newName, transform)
,
Table.ToXml = (t) =>
"<table>"&
{List.Accumulate(
Table.ToRecords(t),
"",
(state,curr)=>state&
"<row>"&
{List.Accumulate(
Record.FieldNames(curr),
"",
(s,c)=>s&Text.Format("<#{0}>#{1}</#{0}>",
{c,
Record.Field(curr,c)})
)}
&"</row>"
)}
&"</table>"
,
Table.NumberColumns = (table as table, columns as list) as table =>
let NumberTypes = List.Repeat({type number}, List.Count(columns)),
DataWithTypes = Table.TransformColumnTypes( table, List.Zip({columns, NumberTypes}) ),
NumberNotNull = (number) => if number is null then 0 else number,
DataNoNulls = Table.TransformColumns( DataWithTypes, List.Zip({ columns, List.Repeat({NumberNotNull}, List.Count(columns)), NumberTypes }) )
in Table.ReplaceErrorValues(DataNoNulls, List.Transform(columns, each {_,0})),
Table.TrimHeader = (Table as table) =>
let hRaw = Table.ColumnNames(Table),
hTrim = List.Transform(hRaw, each Text.Trim(_))
in if List.Count(List.Difference(hRaw, hTrim)) > 0 then Table.RenameColumns(Table, List.Zip({hRaw, hTrim})) else Table
,
// ## Renaming columns
Table.RenameColumn = Document(
"Table.RenameColumn",
"Simple rename 1 column in the table.",
"Table.Transformation",
{[Description="Rename a column in table.",
Code="LPQ[Table.RenameColumn](table, ""old column name"", ""new column name"")",
Result="| new column | column |#(lf)| ---------- | ---------- |" ]},
(Table as table, column as text, newName as text) => Table.RenameColumns(Table, {{column, newName}})
),
Table.RenameColumnsAdv = Document(
"Table.RenameColumnsAdv",
"Advanced rename columns in table by Lists and with optional Filter.",
"Table.Transformation",
{[Description="Rename mane columns in table with possible selection.",
Code="LPQ[Table.RenameColumnsAdv](table, ""{""col1"",""col2""}"", ""{""new1"",""new2""}"", True)",
Result="Table with new column's names and Selected with only renamed columns.#(lf)" &
"| new1 | new2 |" ]},
(Table as table, OldColumns as list, NewColumns as list, optional SelectHeaders as logical) =>
let selectColumns = if (SelectHeaders ?? {SelectHeaders=""}{1}?) = null then false else SelectHeaders,
// Merge Old names and New names
columnsZip = List.Zip({ OldColumns, NewColumns }),
// Get table headers and Select list ny Old names
headers = List.Transform(Table.ColumnNames(Table), each _),
selectHeaders = List.Select(headers, (x) => List.AnyTrue(List.Transform(OldColumns, each x = _))),
// Get only picked columns
SelectTable = if selectColumns then Table.SelectColumns(Table, selectHeaders) else Table.SelectColumns(Table, headers),
// Rename columns
RenameTable = Table.RenameColumns(SelectTable, columnsZip)
in try RenameTable otherwise null),
Table.RenameColumnsByIndices = (Source as table, Indices as list, ColumnNamesNew as list) =>
let ColumnNames = Table.ColumnNames(Source),
ColumnNamesList = List.Transform(Indices, each ColumnNames{_} ),
ZippedList = List.Zip({ ColumnNamesList, ColumnNamesNew } ),
#"Renamed Columns" = Table.RenameColumns(Source, ZippedList )
in #"Renamed Columns",
Table.RenameColumnsByOrder = (InputTable as table, ColumnNumbers as list, NewColumnNames as list) =>
let
OldColumnNames = Table.ColumnNames(InputTable),
Indexed = List.Zip({OldColumnNames, {0..-1+List.Count(OldColumnNames)}}),
Filtered = List.Select(Indexed, each List.Contains(ColumnNumbers,_{1})),
IndexRemoved = List.Transform(Filtered, each _{0}),
RenameList = List.Zip({IndexRemoved,NewColumnNames}),
RenamedColumns = Table.RenameColumns(InputTable, RenameList)
in
RenamedColumns,
Table.TrimHeaders = (Table as table) =>
let hRaw = Table.ColumnNames(Table),
hTrim = List.Transform(hRaw, each Text.Trim(_))
in if List.Count(List.Difference(hRaw, hTrim)) > 0 then Table.RenameColumns(Table, List.Zip({hRaw, hTrim})) else Table,
Table.PromoteHeadersNonEmpty = (table as table) as table =>
/** Promote the first table row as column names, but keep the current header if the value in the first row is empty. For simpler use cases check built-in function `Table.PromoteHeaders` **/
let
ExtraHeaders = Table.First(table),
OldHeaders = Table.ColumnNames(table),
NewHeaders = List.Transform( OldHeaders, (CurrentHeader) =>
let
Current = CurrentHeader,
Candidate = Record.Field(ExtraHeaders, CurrentHeader)
in
if Candidate <> "" and Candidate <> null and Candidate <> 0
then Text.From(Candidate)
else Current
),
RenamedColumns = Table.RenameColumns( Table.Skip(table, 1), List.Zip({OldHeaders, NewHeaders}) )
in RenamedColumns,
Table.SelectHeaders = (List as list, ColumnStart as text, ColumnEnd as text, optional Index as text) =>
let pos1 = List.PositionOf(List, ColumnStart),
pos2 = List.PositionOf(List, ColumnEnd),
range = List.Range(List, pos1 + 1, pos2 - pos1 - 1)
in if Index=null then range else List.Union({{Index}, range}),
Table.RemoveEmptyRows = (tbl as table) =>
// return a table with empty rows removed
let
empties_list = {null, ""}, //null = original null value, "" = after applying text.trim transformation
col_names = Table.ColumnNames(tbl),
tbl_no_erors = Table.ReplaceErrorValues(tbl, List.Transform(col_names, each {_, null})),
//Add helper column
AddedCustom = Table.AddColumn(tbl_no_erors, "Custom",
each
let //convert each record to list and remove empty spaces in each list
record_values = List.Transform(Record.ToList(_), each try Text.Trim(_) otherwise _) //handles numbers
in List.MatchesAll(record_values, each List.ContainsAny(empties_list, {_}) ) //evaluate empties in each record and return a boolean
),
// filter out empty or null record
Filter_out_empty_rows = Table.SelectRows(AddedCustom, each [Custom] = false)
in
Table.RemoveColumns(Filter_out_empty_rows, {"Custom"}),
Table.RemoveEmptyColumns = (tbl as table) =>
// return table with blank columns removed
let
col_names = Table.ColumnNames(tbl), // get column names
tbl_no_erors = Table.ReplaceErrorValues(tbl, List.Transform(col_names, each {_, null})), //remove errors
// fx to check if column has null or empty values
Is_Null_Column_Values = (tbl as table, col_name) =>
let
empty_values_list = {null, ""},
col_values = List.Transform(Table.Column(tbl, col_name), each try Text.Trim(_) otherwise _), //remove spaces
is_null_all = List.MatchesAll(col_values , each List.ContainsAny(empty_values_list, {_}) )
in
is_null_all,
// fx to get col names of empty or null columns
non_empty_columns = (tbl as table)=>
let col_states =
List.Generate(
()=> [col = col_names{count}, count = 0, is_null = Is_Null_Column_Values(tbl, col)],
each [count] < List.Count(col_names),
each [col = col_names{count}, count = [count] + 1, is_null = Is_Null_Column_Values(tbl, col)],
each [ col = if not [is_null] then [col] else null ][col]
)
in List.Select(col_states, each _ <> null )
in
Table.SelectColumns(tbl, non_empty_columns(tbl_no_erors)),
// ## return a table with all errors replaced with specified value or null
Table.ReplaceAllErrorsInTable = (tbl as table, optional replacer_value) => Table.ReplaceErrorValues(tbl, List.Transform(Table.ColumnNames(tbl), each {_, replacer_value}))
,
// ## Sort table
Table.SortColumnByList = Document(
"Table.SortColumnByList",
"Sort table's column text by List.",
"Table.Sorting",
{[Description="Sort table's column text by List.",
Code="LPQ[Table.SortColumnByList](Table, ""Category"", ""{""File"",""Edit""}"", True)",
Result="Sorted table Table in column 'Category' by List sort order Ascending." ]},
(Table as table, ColumnName as text, ListSort as list, Order as logical) =>
Table.RemoveColumns(
Table.Sort(
Table.AddIndexColumn(Table,"i",0,1,Int64.Type), {
{each List.PositionOf(ListSort, Record.Field(_,ColumnName)), if Order then Order.Ascending else Order.Descending },
{"i",Order.Ascending} }
),
{"i"})),
// ## Running Totals
Table.RunningTotalfunction = (TabName as table, RT_Amt_ColumnName as text, Added_Col_RT_Name as text)=>
/**
* TabName = Name of the Table to implement running total on
* RT_Amt_ColumnName = the column with the amount to sum
* Added_Col_RT_Name = custom name for the added column
*/
let
// Amount List to apply List.Generate on
Amt_List = List.Buffer(Table.Column(TabName, RT_Amt_ColumnName)),
// List.Generate calculates the running total
RT_List = List.Generate(
() => [RT = Amt_List{0}, counter = 0],
each [counter] < List.Count(Amt_List),
each [RT = List.Sum({[RT], Amt_List{counter}}), counter = [counter] + 1],
each [RT]
),
// Consolidate brings the table together with the running total
Consolidate = Table.FromColumns(Table.ToColumns(
TabName
) & {RT_List}, Table.ColumnNames(TabName) & {Added_Col_RT_Name}
)
in
Consolidate,
Table.GroupedRunningTotal = (values as list, grouping as list) as list =>
/* Written by Philip Treacy
https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query
*/
let
GRTList = List.Generate
(
()=> [ GRT = values{0}, i = 0 ],
each [i] < List.Count(values),
each try
if grouping{[i]} = grouping{[i] + 1}
then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1]
else [GRT = values{[i] + 1}, i = [i] + 1]
otherwise [i = [i] + 1]
,
each [GRT]
)
in
GRTList
,
// # ARRAYS
// ## VLOOKUP in other table
fnVlookUp = Document(
"fnVlookUp",
"LookUp or CheckUp value in other table (return value from other column or just search)",
"Tables.Search",
{[Description="Value, Table, Search at Column name, Return Column name.",
Code="LPQ[fnVlookUp]([1], Products, ""ProductKey"", ""Price"")",
Result="9000 OR 1" ]},
(lookupValue as any, lookupTable as table, lookupColumnName as text, optional returnColumnValue as text) =>
let colLookup = Table.Column(lookupTable, lookupColumnName),
lookup = List.PositionOf(colLookup, lookupValue, 0),
count = lookup >= 0
in if count then
if returnColumnValue is null then 1
else Table.Column(lookupTable, returnColumnValue){lookup}
else null)
,
// ## Search Name in List
fnCheckInList = Document(
"fnCheckInList",
"Search word in the list. True - case not sesetive, False - case sensetive.",
"Lists.Match",
{[Description="Using separate lists. Params: Word, List, Case sensetive.",
Code="LPQ[fnCheckInList](""Word"", {""Text"", ""Word"", ""Letter""}, true)",
Result="TRUE" ]},
(Name as text, List as list, optional Match as logical) =>
let
match = if (Match ?? {Match=""}{1}?) = null then true else Match,
p = try Text.Trim(Text.BeforeDelimiter(Name,"(")) otherwise Name,
pList = Text.Split(p," "),
result = if match
then List.ContainsAny(pList, List, (x,y) => Text.Contains(x,y,Comparer.OrdinalIgnoreCase))
else List.ContainsAny(pList, List, (x,y) => x=y)
in
try result otherwise null)
,
Text.RegExp = (txt as text, regex as text, delim as text) =>
let RegExp =
Web.Page(
"<script>var x = '" &
txt &
"';var delim = '" &
delim &
"';var regex = /" &
regex &
"/gi;var result = x.match(regex).join(delim);document.write(result);</script>"
)
[Data]{0}[Children]{0}[Children]{1}[Text]{0},
return = try RegExp otherwise null
in
return
,
Text.RegexMatch = (txt as text, regex as text, delim as text) => try
Web.Page("<script>var x='"&txt&"';var delim='"&delim&"';var regex=/"®ex&"/gi;var result=x.match(regex).join(delim);document.write(result);</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
otherwise null
,
Text.GetMatch = (Text as text, List as list) =>
try List.Select(Text.Split(Text," "),
each List.ContainsAny(List.LastN(Text.Split(_," "),1), List, (x,y) => Text.Contains(x,y,Comparer.OrdinalIgnoreCase))
){0}
otherwise null
,
// # TEXT
Text.Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" & "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдежзийклмнопрстуфхцчшщъыьэюя",
Text.AlphaNumeric = Text.Alphabet & "0123456789",
Text.FromList = (list as list) => List.Accumulate(list, "", (state, current) => state & Text.From(current)),
Text.IsNumber = (text as text) => try Number.FromText(text) is number otherwise false
,
// ## Is text all uppercase? returns false if any non-alpha characters are present
Text.IsUpperCase = (text as text) => List.AllTrue(List.Transform(Text.ToList(text), (letter)=>Text.Contains(Text.Alphabet, letter) and letter = Text.Upper(letter))),
Text.IsAlpha = (text as text) => List.MatchesAll(Text.ToList(text), each Text.Contains(Text.Alphabet, _)),
Text.RemoveExtraWhitespace = (text as text) => Text.Combine(Splitter.SplitTextByWhitespace()(text)," ")
,
// ## Splits camelCased and PascalCased text and separates by a space. Ex: "thisIsAColumn" -> "this Is A Column"
Text.SplitCamelCase = (text as nullable text) => if text is null then null else List.Accumulate(Text.ToList(text),"", (state, current) =>
let
PreviousLetter = Text.End(state, 1),
Ignore = (text as text) => text = " " or text = "."
in state & (if not Text.IsUpperCase(PreviousLetter)
and not Ignore(PreviousLetter)
and not Ignore(current)
and Text.IsUpperCase(current)
then " " else "" ) &
current),
Text.SplitOnNotIn = (line as nullable text, validCharacters as text) => Splitter.SplitTextByNotIn(validCharacters)(line),
Text.SplitOnNonAlpha = (line as nullable text) =>
if line is null then null else List.Accumulate(Text.ToList(line), {null} , (state, current) =>
let
doSkip = not Text.Contains(Text.Alphabet, current),
lastItem = List.Last(state),
appendLast = lastItem<>null
in
if doSkip then
if lastItem is null then state
else List.Combine({state, {null}})
else if appendLast
then List.Combine({List.RemoveLastN(state, 1), {lastItem & current}})
else List.Combine({List.RemoveLastN(state, 1), {current}})),
Text.Substring = (text as text, start as number, optional count as number) =>
let
start = if start >= 0 then start else error "start index should be >= 0",
end = if count = null then Text.Length(text) else if count <= Text.Length(text) then count else error "count should be <= text length",
textList = Text.ToList(text),
substr = Text.FromList(List.FirstN(List.Skip(textList, start), end - start))
in substr,
Text.PositionAfter = (text as nullable text, substring as text) =>
let
firstIndex = Text.PositionOf(text, substring),
indexAfter = if firstIndex >=0 then firstIndex + Text.Length(substring) else -1
in
if text is null then -1 else if indexAfter >= 0 and indexAfter < Text.Length(text) then indexAfter else -1,
Text.Until = (text as text, endDelimiter as text, optional startIndex as number) =>
let
start = if startIndex = null then 0 else startIndex,
textFromStart = Text.Substring(text, start),
delimPosition = if Text.PositionOf(textFromStart, endDelimiter) >= 0 then Text.PositionOf(textFromStart, endDelimiter) else Text.Length(textFromStart)
in
if text is null then null else Text.Range(textFromStart, 0, delimPosition),
Text.AsciiOnly = (s as text) as text =>
let
Listified = Text.ToList(s),
Numbered = List.Transform(Listified, each Character.ToNumber(_)),
Filtered = List.Select(Numbered, each _ <= 255),
Stringified = List.Transform(Filtered, each Character.FromNumber(_)),
Joined = Text.Combine(Stringified, ""),
Return = Joined
in
Return,
Text.Between = (Haystack as text, After as text, Before as text) as text =>
let
CutAfter = Text.Split(Haystack, After),
CutBefore = Text.Split(CutAfter{1}, Before),
Needle = if List.Count(CutAfter) > 1
then (if List.Count(CutBefore) > 1 then CutBefore{0} else Error.Record("FindTextFailed","The text did not contain the keyword " & Before, Haystack))
else error Error.Record("FindTextFailed","The text did not contain the keyword " & After, Haystack)
in Needle,
Text.ContainsAny = (str as text, needles as list) as logical =>
let count = List.Count(needles)
in List.AnyTrue(
List.Generate(
()=>[i=0],
each [i] < count,
each [i=[i]+1],
each Text.Contains(str,needles{[i]}) )),
Text.Count = (Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1,
Text.EachBetween = (Haystack as text, After as text, Before as text) as list =>
let
CutAfter = Text.Split(Haystack, After),
SkipFirst = List.Skip(CutAfter),
CutEach = List.Transform(SkipFirst, each Text.Split(_, Before){0})
in
CutEach,
Text.EachFromTo = (Haystack as text, After as text, Before as text) as text =>
let CutAfter = Text.Split(Haystack, After),
SkipFirst = List.Skip(CutAfter),
CutEach = List.Transform(SkipFirst, each After & Text.Split(_, Before){0} & Before)
in CutEach,
Text.FromTo = (Haystack as text, From as text, UpTo as text) as text =>
let
CutAfter = Text.Split(Haystack, From),
CutBefore = Text.Split(CutAfter{1}, UpTo),
Needle = if List.Count(CutAfter) > 1
then (if List.Count(CutBefore) > 1 then From & CutBefore{0} & UpTo else Error.Record("FindTextFailed","The text did not contain the keyword " & UpTo, Haystack))
else error Error.Record("FindTextFailed","The text did not contain the keyword " & From, Haystack)
in Needle,
Text.RemoveSymbols = (inputtext as text) as text =>
let
//get a list of lists containing the numbers of Unicode punctuation characters
numberlists = {{0..31},{33..47},{58..64},{91..96},{123..191}},
//turn this into a single list
combinedlist = List.Combine(numberlists),
//get a list of all the punctuation characters that these numbers represent
punctuationlist = List.Transform(combinedlist, each Character.FromNumber(_)),
//some text to test this on
//inputtext = "Hello! My name is Chris, and I'm hoping that this *cool* post will help you!",
//the text with punctuation removed
outputtext = Text.Remove(inputtext, punctuationlist)
in
outputtext,
Text.ReplaceAll = (str as text, Replacements as list) as text =>
let count = List.Count(Replacements)
in List.Last(
List.Generate(
()=>[i=0, s=str],
each [i] <= count,
each [
s=Text.Replace([s],Replacements{[i]}{0},Replacements{[i]}{1}),
i=[i]+1
],
each [s] )),
Text.PowerTrim = Document(
"Text.PowerTrim",
"Power Trim all text.",
"Text.Transform",
{[Description="Power Trim all text.",
Code="LPQ[Text.PowerTrim]("" A B C "")",
Result="A B C" ]},
(text as text, optional char_to_trim as text) =>
let char = if char_to_trim = null then " " else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> "")
in Text.Combine(removeblanks, char)),
Text.ReplaceByList = try (txt as text, replacementLists as list) as text => List.Accumulate(replacementLists, txt, (s,x) => Text.Replace(s,x{0},x{1})) otherwise null
,
//[ ]: add description
Text.ContainsList =
(Text as text, List as list, optional ContainsMatch as logical) =>
let
match = ContainsMatch ?? {ContainsMatch=""}{1}? ?? false,
product = try Text.Trim(Text.BeforeDelimiter(Text.Lower(Text),"(")) otherwise Text.Lower(Text),
pList = Text.Split(product, " "),
compareList = List.Transform(List, Text.Lower),
result =
if match then
List.ContainsAny(pList, compareList, (x,y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase))
else
List.ContainsAny(pList, compareList, (x,y) => x=y)
in