how to transpose excel data source has 2 common field

[Anonymous]

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.

[Ashish_Mathur · Accepted 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.

[amitchandak]

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

[Anonymous]

@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

[Jimmy801]

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

[Jimmy801]

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