cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbangari001
Frequent Visitor

Please help me with cross table

Hi, I have the requirement where I need to convert the multiple columns into single month column. Please see the below excel.

 

ProcessIDProcessNameidLOSVendorIDTerritoryDateDecommissionedForecastVolume_JanForecastVolume_FebForecastVolume_MarchForecastVolume_April
10abc131abc9IFS1USNULL52762227493
12asdfghabc10IFS1USNULL2006null1685null
13asdfghabc11IFS1US1/5/2021352828

32

 

 

 

 

This is how I want it.

 

ProcessIDProcessNameidLOSVendorIDTerritoryDateDecommissionedForecast MonthHours
10abc131abc9IFS1USNULLJan527
12asdfghabc10IFS1USNULLJan2006
13asdfghabc11IFS1US1/5/2021Jan25
10abc131abc9IFS1USNULLFeb622
12asdfghabc10IFS1USNULLFebnull
13asdfghabc11IFS1US1/5/2021Feb28
10abc131abc9IFS1USNULLMarch274
12asdfghabc10IFS1USNULLMarch1685
13asdfghabc11IFS1US1/5/2021March28
10abc131abc9IFS1USNULLApril93
12asdfghabc10IFS1USNULLaprilnull
13asdfghabc11IFS1US1/5/2021april32

 

please help. Crosstable layout ? 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @rbangari001 ,

 

this is my sample data:

image.png

The task you have to solve is converting a table from the wide format into a table in the long format, meaning converting columns into rows.

 

For this reason, I mark all columns (in my example it's just the column this or that), except the columns ForecastValue_... and select the operation from the context menu Unpivot Other Columns:

image.png

The next picture shows the result of the operation:

image.png

Then I split the column now called Attribute, rename the columns accordingly and delete the column that contains the characters fc or ForecastValue respectively.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @rbangari001 ,

 

this is my sample data:

image.png

The task you have to solve is converting a table from the wide format into a table in the long format, meaning converting columns into rows.

 

For this reason, I mark all columns (in my example it's just the column this or that), except the columns ForecastValue_... and select the operation from the context menu Unpivot Other Columns:

image.png

The next picture shows the result of the operation:

image.png

Then I split the column now called Attribute, rename the columns accordingly and delete the column that contains the characters fc or ForecastValue respectively.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Thank you, it worked

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!