topic Re: Merge Table Columns - Chnaging from a WIDE format to a LONG data format in DAX Commands and Tips

Merge Table Columns - Chnaging from a WIDE format to a LONG data format

HLST — Sun, 05 Dec 2021 23:58:46 GMT

## 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

Re: Merge Table Columns - Chnaging from a WIDE format to a LONG data format

VahidDM — Fri, 26 Nov 2021 00:07:20 GMT

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

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

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/

Re: Merge Table Columns - Chnaging from a WIDE format to a LONG data format

v-robertq-msft — Mon, 29 Nov 2021 08:10:55 GMT

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.