Unpivot Multiple data in same column

Hello Anonymous 

Try out this solution. Important thing to note... database has to start at the first row. Therefore I added column header with Column1 etc.

let
	Source = #table
	(
		{"Column1","Column2","Column3"},
		{
			{"Date              "," Company1      "," Company2"},	{"01-02-2019   "," 1000               ","2000"},	{"01-02-2019   "," 1000               ","2000"},	
			{"01-02-2019   "," 1000               ","2000"},	{"01-02-2019   "," 1000               ","2000"},	{"Date              "," Company1    "," Company4"},	
			{"01-02-2018   "," 1000               ","2000"},	{"01-02-2018   "," 1000               ","2000"},	{"01-02-2018   "," 1000               ","2000"},	{"01-02-2018   "," 1000               ","2000"}

					}
	),
    TrimText = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}}),
    AddIndex = Table.AddIndexColumn(TrimText, "Index", 0, 1),
    CreateGroupIndicator = Table.AddColumn(AddIndex, "CheckDate", each if [Column1]="Date" then [Index] else null),
    FillDown = Table.FillDown(CreateGroupIndicator,{"CheckDate"}),
    DeleteIndex = Table.RemoveColumns(FillDown,{"Index"}),
    Group = Table.Group(DeleteIndex, {"CheckDate"}, {{"AllRows", each _, type table [Column1=text, Column2=text, Column3=text, CheckDate=number]}}),
    DeleteGroupIndicator = Table.RemoveColumns(Group,{"CheckDate"}),
    PromoteHeader = Table.TransformColumns
    (
        DeleteGroupIndicator,
        {
            
            {"AllRows", each Table.PromoteHeaders(Table.RemoveColumns(_, {"CheckDate"}))}
        }
    ),
    Combine = Table.Combine( PromoteHeader[AllRows]),
    UnpivotOther = Table.UnpivotOtherColumns(Combine, {"Date"}, "Attribut", "Wert")
in
    UnpivotOther

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Thanks!!

That worked. I'm not very used with powerQuery, but I will try to adapt it to my file.

Can you please explain me what does this part of the code does?

PromoteHeader = Table.TransformColumns
    (
        DeleteGroupIndicator,
        {
            
            {"AllRows", each Table.PromoteHeaders(Table.RemoveColumns(_, {"CheckDate"}))}
        }
    ),

Hello Anonymous 

I would appreciate you marking the post a solution 🙂

The "AllRows" Column of the prior step contains all tables (your big table split into your real tables). This part of the code is trasforming very cell (in this case every table). It applies two changes... fist, removes a column, that it's needed only to accomplish something in the prior steps, and than promotes the first data row as header.

Jimmy