Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a file where each group of entries on a given date is separated by two headers: one with the date and another with the location (in italics and underline below):
Obviously, I want to pull the date and location data out as additional columns and fill each row of those new columns with the corresponding data. There is no set number of entries between each grouping.
Solved! Go to Solution.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type any}, {"Amount", type number}, {"Customer", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Account"},
{{"Rows",
each
let
ct = Table.RowCount(_)
in
Table.FromColumns(
Table.ToColumns(Table.Skip(_,2))
& {List.Repeat({_[Account]{0}},ct-2)}
& {List.Repeat({_[Account]{1}},ct-2)},
Table.ColumnNames(_) & {"Date","Location"}
)
}},
GroupKind.Local,
(x,y) => Byte.From(try Date.From(y[Account]) is date otherwise 0)
)
in
#"Grouped Rows"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type any}, {"Amount", type number}, {"Customer", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Account"},
{{"Rows",
each
let
ct = Table.RowCount(_)
in
Table.FromColumns(
Table.ToColumns(Table.Skip(_,2))
& {List.Repeat({_[Account]{0}},ct-2)}
& {List.Repeat({_[Account]{1}},ct-2)},
Table.ColumnNames(_) & {"Date","Location"}
)
}},
GroupKind.Local,
(x,y) => Byte.From(try Date.From(y[Account]) is date otherwise 0)
)
in
#"Grouped Rows"