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 all
i have this excel data source about sales of products A,B,C,D in 2 different location.
possible to transpose the data using the existing data?
the excel come from another dept and if possible we try not to change the excel,but not sure if feasible?
I tried transpose the data in various ways but can't seem to get it right.
any advice? Thank you so much.
Solved! Go to Solution.
Hi,
As suggested by Amit, create a spare column and give that a heading as City. Type Products in cell A1. Load each Table individually to the Query Editor. In the Query Editor, select the Products and City column, right click and select "Unpivot other columns".
Hope this helps.
Hi,
As suggested by Amit, create a spare column and give that a heading as City. Type Products in cell A1. Load each Table individually to the Query Editor. In the Query Editor, select the Products and City column, right click and select "Unpivot other columns".
Hope this helps.
Add a new column in both excel after import
city ="Give city name of that excel"
after that, you can merge and transpose or transpose and merge
refer
https://radacad.com/pivot-and-unpivot-with-power-bi
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
@amitchandak
in my case, both table is in the same excel sheet (as in sheet1). is it still possible to transpose?I tried but couldnt get it right.
could you advice? thank you
Hello
have you been able to solve the problem with my solution?
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
tried to prepare a solution for you.
There are two important steps.. first able to group the tables and to bring the place into a column. Check out this solution. The only thing that is not dynamic here are the column names of the months.
let
Source = #table
(
{"Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10","Column11","Column12"},
{
{"Arizona","Jan 16","Feb 16","Mrz 16","Apr 16","Mai 16","Jun 16","Jul 16","Aug 16","Sep 16","Okt 16","Nov 16"}, {"Product A","59","193","133","107","152","138","61","181","72","193","134"},
{"Product B","174","112","157","66","174","129","99","87","153","143","105"}, {"Product C","174","86","122","90","99","119","137","155","195","95","190"},
{"Product D","154","52","154","128","170","142","189","115","140","187","183"}, {"","","","","","","","","","","",""}, {"","","","","","","","","","","",""},
{"llinois","Jan 16","Feb 16","Mrz 16","Apr 16","Mai 16","Jun 16","Jul 16","Aug 16","Sep 16","Okt 16","Nov 16"}, {"Product A","106","102","58","134","119","153","141","122","197","54","67"},
{"Product B","84","67","85","60","110","154","50","107","172","112","55"}, {"Product C","104","130","108","64","146","129","73","156","54","60","124"},
{"Product D","149","138","94","67","173","152","182","157","90","190","160"}
}
),
CreateEmpptyRowWithColumn= #table({"Column1"}, {{""}}),
CombineWIthEmpty = Table.Combine({Source,CreateEmpptyRowWithColumn}),
AddIndex = Table.AddIndexColumn(CombineWIthEmpty, "Index", 1, 1),
AddCheck = Table.AddColumn(AddIndex, "CheckTable", each if [Column1]="" then [Index] else null),
FillUp = Table.FillUp(AddCheck,{"CheckTable"}),
Group = Table.Group(FillUp, {"CheckTable"}, {{"AllRows", each _, type table [Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, Column7=text, Column8=text, Column9=text, Column10=text, Column11=text, Column12=text, Index=number, CheckTable=number]}}),
TransformTableFunction = (tableint) =>
let
DeleteIndex = Table.RemoveColumns(tableint, "Index"),
DeleteEmpty = Table.SelectRows(DeleteIndex, each ([Column1] <> "")),
AddIndex = Table.AddIndexColumn(DeleteEmpty, "Index", 0, 1),
AddPlaceTemp = Table.AddColumn(AddIndex, "Placetemp", each if [Index]=0 then [Column1] else null),
FillDown = Table.FillDown(AddPlaceTemp,{"Placetemp"}),
AddPlace = Table.AddColumn(FillDown, "Place", each if [Index]= 0 then "Place" else [Placetemp]),
AddProduct = Table.AddColumn(AddPlace, "Product", each if [Index]= 0 then "Product" else [Column1]),
Header = Table.PromoteHeaders(AddProduct, [PromoteAllScalars=true]),
DeleteOther = Table.SelectColumns(Header,{"Product", "Place", "Nov 16", "Okt 16", "Sep 16", "Aug 16", "Jul 16", "Jun 16", "Mai 16", "Apr 16", "Mrz 16", "Feb 16", "Jan 16"})
in
try DeleteOther otherwise null,
Transform = Table.TransformColumns
(
Group,
{
{
"AllRows",
each TransformTableFunction(_),
type table
}
}
),
DeleteOther = Table.SelectColumns(Transform,{"AllRows"}),
Expand = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Product", "Place", "Nov 16", "Okt 16", "Sep 16", "Aug 16", "Jul 16", "Jun 16", "Mai 16", "Apr 16", "Mrz 16", "Feb 16", "Jan 16"}, {"Product", "Place", "Nov 16", "Okt 16", "Sep 16", "Aug 16", "Jul 16", "Jun 16", "Mai 16", "Apr 16", "Mrz 16", "Feb 16", "Jan 16"}),
Filter = Table.SelectRows(Expand, each ([Product] <> null))
in
Filter
Copy paste this code to the advanced editor in a new blank query 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.
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
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.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |