Forum Discussion
Merge Table Columns - Chnaging from a WIDE format to a LONG data format
## IMAGES REMOVED
Hi all,
I am looking to produce a clustered bar chart with product type on the x-axis. My data is in the format displayed in the attached image. I would like to create a new table in BI where I have COL.1 - Time, COL.2 - Product type, COL.3 - Units sold, COL.4 - Jurisdiction. To do so, I need to merge all product columns into a single column creating many more rows to express sales of products in each month by respective region. I have attached a screenshot to illustrate what I am looking for.
To put it simply, my data exists in WIDE format which limits me from presenting certain variables without printing x number of graphs. I would like to transform my WIDE data into a LONG format so my data is compatible with all plot types.
Greg_Deckler amitchandak AlexisOlson
HI HLST
Can you first Unpivot the Green columns of your table in the Power Query?
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you can add some custom column and achive that output.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
2 Replies
- VahidDM
Super User
HI HLST
Can you first Unpivot the Green columns of your table in the Power Query?
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you can add some custom column and achive that output.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/ - v-robertq-msft
Community Support
Hi,
According to your description, I can roughly understand your requirement, you want to create a new table in BI to convert the four columns in the product table into one column and display it in the new table, right? I think you can try my method:
This is the test data I created based on your description:
First, create a calculated column in the original table:
LONG format = COMBINEVALUES("-",[Time],[Jurisdictio],[Product],[Units_Sold])Then create a new table like this:
LONG format = SUMMARIZE('Table','Table'[LONG format])And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
