Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.