/
fn_CombineTopNRows
42 lines (42 loc) · 1.95 KB
/
fn_CombineTopNRows
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
let
func = (InputTable as table, TopNRows as number, optional Del as nullable text) as table =>
let
Delimiter = if Del <> null then Del else "#(lf)",
ToTables = Table.SplitAt(InputTable, TopNRows),
OldName = Table.ColumnNames(InputTable),
Transpose = Table.Transpose(ToTables{0}),
TrColN = Table.ColumnNames(Transpose),
ToText = Table.TransformColumnTypes(
Transpose,
List.Transform(TrColN, each {_, type text})
),
FillDown = Table.FillDown(ToText,TrColN),
ToColumns = Table.ToRows(FillDown),
NewName = List.Transform(ToColumns, each Text.Combine(_, Delimiter)),
CombineColN = List.Zip({OldName,NewName}),
RenColName = Table.RenameColumns(ToTables{1},CombineColN)
in
RenColName,
// Add documentation
documentation = [
Documentation.Name = " fn_CombineTopNRows ",
Documentation.Description = " Returns a Table with Combining X Number of Rows. ",
Documentation.LongDescription
= " Returns a Table with Combining X Number of Rows and Promote them as header. ",
Documentation.Category = " Table ",
Documentation.Source
= " https://www.biinsight.com/combining-x-number-of-rows-in-power-query-for-power-bi-excel-and-power-query-online/ ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Muneer Marzouq, Mahmoud Bani Asadi, Soheil Bakhshi ",
Documentation.Examples = {
[
Description
= " see this blogpost: https://www.biinsight.com/combining-x-number-of-rows-in-power-query-for-power-bi-excel-and-power-query-online/ ",
Code
= " fn_CombineTopNRows(#table({""Column1"", ""Column2"", ""Column3""}, {{""TitleA"", ""TitleC"", ""Title1""}, {""TitleB"", ""TitleD"", ""Title2""}, {7, 8, 9}}), 2, "" "")",
Result = " #table({""TitleA TitleB"", ""TitleC TitleD"", ""Title1 Title2""}, {{7, 8, 9}}) "
]
}
]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))