Generalizing a table with column reduction

[jb257]

Dear community,

actually i want to solve an easy task but i am struggling and hope that someone can help me.

I have the following table:

Alfred	Alfred_1	Alfred_2	Bert	Bert_1	Bert_2	Claudia	Claudia_1	Claudia_2
01.01.2023	01.06.2023	10	01.06.2023	01.03.2024	100	01.01.2023	01.07.2023	4
01.02.2023	01.06.2023	20	01.07.2023	01.03.2024	110	01.02.2023	01.07.2023	5
01.03.2023	01.06.2023	30	01.08.2023	01.03.2024	120	01.03.2023	01.07.2023	6
01.04.2023	01.06.2023	40	01.09.2023	01.03.2024	130	01.04.2023	01.07.2023	7
01.05.2023	01.06.2023	50	01.10.2023	01.03.2024	140	01.05.2023	01.07.2023	8
			01.11.2023	01.03.2024	150	01.06.2023	01.07.2023	9
			01.12.2023	01.03.2024	160
			01.01.2024	01.03.2024	170
			01.02.2024	01.03.2024	180

and i want to transform the table to the following:

Start	End	Value	Name
01.01.2023	01.06.2023	10	Alfred
01.02.2023	01.06.2023	20	Alfred
01.03.2023	01.06.2023	30	Alfred
01.04.2023	01.06.2023	40	Alfred
01.05.2023	01.06.2023	50	Alfred
01.06.2023	01.03.2024	100	Bert
01.07.2023	01.03.2024	110	Bert
01.08.2023	01.03.2024	120	Bert
01.09.2023	01.03.2024	130	Bert
..	..	..	..

I would be happy to receive all solutions

Greetings jb257

[dufoq3]

You can also use this version. Just change 2nd step YourSource = Source to your data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJdDoQgDATgqxiezab0B/AsxvtfYwVto+wkm0jigPP1QfY9Uf6cDxNLWkcoHjLNOz1IDzqO/fxVrx40HevFM+SZ5sabj/EMeQteIC9eb5iP8QL5ErxCXr2+YT7GK+Rr8AZ5u+uZMB/jDfJt8Mv55qtbGVsG/3NYG7QYW4Ue3y2oed0W/WnW/03GzTY1jy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Alfred = _t, Alfred_1 = _t, Alfred_2 = _t, Bert = _t, Bert_1 = _t, Bert_2 = _t, Claudia = _t, Claudia_1 = _t, Claudia_2 = _t]),
    YourSource = Source,
    #"Added Index" = Table.AddIndexColumn(YourSource, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    Ad_Name = Table.AddColumn(#"Unpivoted Other Columns", "Name", each Text.BeforeDelimiter([Attribute], "_"), type text),
    #"Grouped Rows" = Table.Group(Ad_Name, {"Name"}, {{"All", each _, type table [Index=number, Attribute=text, Value=text, Name=text]}, {"Start", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name])[Value], type list}, {"End", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_1")[Value], type list}, {"Value", each Table.SelectRows(_, (r)=> r[Attribute] = r[Name] & "_2")[Value], type list}}),
    Ad_MergedListsToTable = Table.AddColumn(#"Grouped Rows", "MergedListsToTable", each Table.SelectRows(Table.FromColumns({[Start], [End], [Value]}, {"Start", "End", "Value"}), (r)=> Text.Trim(r[Start]) <> null and Text.Trim(r[Start]) <> ""), type table),
    #"Removed Other Columns" = Table.SelectColumns(Ad_MergedListsToTable,{"MergedListsToTable", "Name"}),
    #"Expanded MergedTables" = Table.ExpandTableColumn(#"Removed Other Columns", "MergedListsToTable", {"Start", "End", "Value"}, {"Start", "End", "Value"})
in
    #"Expanded MergedTables"

[slorin · Accepted solution]

Hi,

let
Source = Your_Source,
Name = List.Alternate(Table.ColumnNames(Source),2,1,1),
Data = List.Transform(List.Split(Table.ToColumns(Source),3), each Table.FromColumns(_, {"Start", "End", "Value"})),
#"Data+Name" = Table.FromColumns({Data}&{Name}, {"Data", "Name"}),
Expand = Table.ExpandTableColumn(#"Data+Name", "Data", {"Start", "End", "Value"}, {"Start", "End", "Value"}),
NotNull = Table.SelectRows(Expand, each [Start] <> null)
in
NotNull

 Stéphane

[jb257]

Awesome, thank you very much!