cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Change rows to columns

Hi all,

I'm a relatively new to Power BI and am trying to find a solution that will allow me to display a table where each line is grouped by a unique transaction number, and where products are listed as columns instead of rows, to enable a simpler view of the data. 

Below is what my data looks like:
1.PNG

 

There are a maximum of 4 items per transaction. The data table labels each row between 1-4.

I want my final result to look something like this, with 4 columns that will display the product name (of which there could be hundreds) and quantity:

2.PNG

Would really appreciate any suggestions on how to get the desired results! Thanks in advance.

Mo 



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Change rows to columns

@Mohs - Take a look at my file link again, second tab. I created 1 measure that is a simple SUM forumula. I then put it in a Matrix, and put the line number in the columns, everything else in rows, and got rid of the subtotals, except for the Transction line, which I thought might be useful, but you can get rid of that too. Then expanded all row descriptions and removed the stepped layout default.

edhans_1-1597080663394.png

That will automatically expand for every line item, so if a transaction comes in with 6 line items, you'll have 6 columns of data.

You could put the product names in the columns, but you have 13 unique products, so that becomes a very wide table to show all 13, and I suspect your real data has even more actual products.

 

You could combine this process with the measure @MFelix provided if you don't want actual quantities but the concatenation of the product name and total in the Values part. I did a rough version of that on tab 3.


You can pre-expand the entire matrix so your users don't have to, and remove the +/- expansion buttons if desired, also shown on tab 3

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

13 REPLIES 13
Highlighted
Continued Contributor
Continued Contributor

Re: Change rows to columns

Hi @Mohs 

 

Go to powerquery and create blank query and paste below in the advance editor and see the steps, this will provide you exactly what you want, let me know if not clear.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEIDZVitWBiBgBcRJcxAhDjRFWNcZAnIwiYgLEKXARY6g5qSgiIHVpKCIgnI4iAjInAyISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, Seuquence = _t, Product = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", Int64.Type}, {"Seuquence", Int64.Type}, {"Product", type text}, {"Qty", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Qty", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Products", each [Product]&[Qty]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Product", "Qty"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Seuquence", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "prod", each "Poduct" & " "&[Seuquence]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Transaction"}, {{"combine", each Text.Combine([Products],"; "), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "combine", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"combine.1", "combine.2", "combine.3", "combine.4"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"combine.1", type text}, {"combine.2", type text}, {"combine.3", type text}, {"combine.4", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"combine.1", "Product1"}, {"combine.2", "Product2"}, {"combine.3", "Product3"}, {"combine.4", "Product4"}})
in
#"Renamed Columns"

Highlighted
Super User V
Super User V

Re: Change rows to columns

Hi @Mohs ,

 

Create a matrix visualization and a measuare with the following formats and syntax:

Values = MAX('Table'[Product]) & "-" & SUM('Table'[Quantity])

 

  • Rows:
    • Date
    • Transaction
  • Columns:
    • Sequence
  • Values
    • Measure Values
  • Turn off Steped layout

MFelix_0-1596753127721.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Super User VI
Super User VI

Re: Change rows to columns

@Mohs , the code @mhossain provided will work, but this is not a good practice to do. You have a nice normalized table and you are denormalizing it. You will potentially have dozens, hundreds, or even thousands of coluimns over time.

 

You should instead create a DIM table. You are trying to make your FACT table your DIM table as well.

  1. Create a reference to your main table
  2. Select the Transactions column and remove other columns
  3. Remove duplicates from the transaction column.
  4. This becomes your Dimension

Unique Values.gif

Let us know what your ultimate goal is - the visual or report output. We can help you get there. De-normalizing your data though will make the DAX very difficult later on and it will not be dynamic as new transaction types come in. Stick to a Star Schema, which has a  normalized FACT table.

 

Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Continued Contributor
Continued Contributor

Re: Change rows to columns

Hi @edhans 

 

I thought product columns will be fixed to 4, as per @Mohs requirement that max only 4 transaction ID for any records, maybe I missed it, please check.

 

That code was not cleaned one just for reference and keeping in mind above and the data/report output.

 

I agree with you on the data strucutring side.

Highlighted
Super User VI
Super User VI

Re: Change rows to columns

Yes, you are right @mhossain - I was more focused on "why is this table being denormalized?" 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper I
Helper I

Re: Change rows to columns

Many thanks @edhans for the reply - 

My ultimate goal is to create a visual that displays a product pick-up schedule.

The visual needs to be a table with the following columns (left to right) - I omitted some of these columns in my original post for the sake of simplicity but want to make sure I am able to display multiple data from each transaction.

Column 1 : Date and load start time
Column 2:  Product load end date and time
Column 3: Transaction
Column 4: Customer
Column 5: Physical destination of product
Column 6: Product 1
Column 7: Product 2
Column 8: Product 3
Column 9: Product 4
Column 10: Special instructions?notes

Appreciate your support,
Mohs

Highlighted
Super User VI
Super User VI

Re: Change rows to columns

Hi @Mohs ,

 

You can create measures that will filter your data by the product type. For example:

 

Product A = 
VAR varProductName = "A"
VAR Result = 
CALCULATE(
    SUM('Table'[Qty]),
    'Table'[Product] = varProductName
)
RETURN
    Result

 

Then simply drop this 'Product A' measure into a column on your table. Repeat for B, C, D, etc.

You didn't have a lot of the columns you wanted in your sampled data (customer, special instructions, etc.) but those would likely just shake out to be on the correct row for a given measure. I have created the Product A and B measures and it returns this:

edhans_0-1597073430742.png

 

But your data is still fully normalized. In fact, I didn't do any transformations to your original data.

edhans_1-1597073168282.png

Attached is my PBIX file if you want to take a look. Note that I am using a date table, and the date in the table visual is from that, not your data table. This will allow time intelligence if you want - vs last month, prior year, whatever. 

If you need more help, please provide a representative set of data in a usable (not image) format, and exactly what you want the output to look like, but hopefully this will get you started.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper I
Helper I

Re: Change rows to columns

Hi @edhans ,

I've attached a sample data table for you mimicing what I'm working with. 

Note that importantly, I woulk like each Transaction ID on a single row (something like my mock-up in my original post), with 4 product placeholder columns. The actual products contained within those columns are more than 4, but there will likely not be (never say never though) more than 4 products per transactions.

Data: Sample data 

Hopefully that helps explain what I'm after. Thanks again!


Highlighted
Helper I
Helper I

Re: Change rows to columns

This is a great solution!

One question though: I would like my final visual to be a table and not a matrix as I don't want to have users have to work with having to expand a table using the '+' icon on the matrix. Do you have any suggestions on how to accomplish that? 

Best regards,
Mohs


Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors