Unpivot Multiple data in same column

[Anonymous]

Hi

I have a file with multiple columns, where each column can be one or more companies. Something like the following format:

Date              | Company1      | Company2

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

...... (400 rows about)

Date              | Company 1    | Company 4

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

I need to unpivot this, to something like this:

Date              |  Company       |  Amount

01-02-2018   | Company1      | 1000

01-02-2018   | Company2      | 2000

01-02-2018   | Company3      | 2000

01-02-2018   | Company4      | 2000

.....

How can I achieve this? Is there any way?

BTW, This is an example of the file:

https://www.spensiones.cl/apps/valoresCuotaFondo/vcfAFPxls.php?aaaaini=2002&aaaafin=2019&tf=A&feccon...

In other post, I've seen that it is possible by splitting it manually... but I need to update this every day, so manual is not a good choice.

Thanks!

[Jimmy801 · Accepted solution]

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

[Anonymous · Accepted solution]

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"}))}
        }
    ),

[Jimmy801]

Hello @Anonymous 

have you been able to solve the problem with the replies given?

If so, 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

All the best

Jimmy

[Jimmy801 · Accepted solution]

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

[Anonymous · Accepted solution]

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"}))}
        }
    ),

[Jimmy801 · Accepted solution]

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