How to Transpose Article Expiration Dates and Quantities in Power BI
Question details
The user wants to transform a Power BI source table so each article appears once, with all related expiration dates and quantities displayed in a single row or combined format.
- Product
- Microsoft Power BI / Power Query
- Device & OS
- not provided
- Scenario
- The user has a source table in Power BI where each article appears in multiple rows with different expiration dates and quantities and wants one row per article without losing the related values.
- Observed behavior
- Each article is currently represented in multiple rows with separate expiration dates and quantities; the goal is one row per article with all related values displayed together.
Before you start, confirm the exact column names for article, expiration date, and quantity in your source table. Because the original attachment is missing, prepare a small representative sample file for testing the Power Query grouping steps.
Group Articles in Power Query and Combine Related Expiration Dates and Quantities
Use this method when each article has multiple rows and you need one row per article while preserving all expiration date and quantity values.
Power Query can group rows by article and aggregate the related values into lists or delimited text. The exact column names depend on your data model, so adjust them to match your source data.
In Power BI Desktop, click Home > Transform data to open Power Query Editor.
Select the table that contains article, expiration date, and quantity columns, or use Home > Get data to load the source file.
On the Transform tab, click Group by, choose the Article column as the group key, and add grouped columns for Expiration Date and Quantity.
For each grouped column, choose Sum, Count, All Rows, or Text.Combine depending on whether you need totals, lists, or comma-separated expiration dates and quantities.
Click Home > Close & Apply to load the grouped result into Power BI.
Use WPS Office to Prepare and Clean Source Data for Power BI
WPS Office does not provide a native Power BI replacement, but it can help you open, clean, and prepare the underlying Excel or CSV source files before importing them into Power BI—with a familiar interface, full Microsoft Office format compatibility, and seamless migration.
- 1. Download WPS Office: Go to the official WPS Office website and click Download WPS Office Free to install the suite.
- 2. Open the source workbook: Launch WPS Office, click Open, and select the Excel or CSV file that contains the article expiration dates and quantities.
- 3. Clean and save the data: Use WPS Spreadsheets to remove unneeded columns, correct data types, and save the file as an Excel or CSV file for Power BI import.
Frequently Asked Questions
Why is my attachment needed for a Power Query solution?
Without the sample file or representative data structure, the exact column names and desired output format are unknown, so the Power Query grouping steps can only be given in general form.
Can Power Query group rows by article and keep all expiration dates?
Yes. In Power Query Editor, use Transform > Group by, choose the article column as the group key, and add a grouped column that uses All Rows or Text.Combine to keep individual expiration dates.
How do I show multiple expiration dates and quantities in one cell in Power BI?
Group rows by article in Power Query, then create a grouped column that uses Text.Combine or list values to join the related expiration dates and quantities in a single cell.
What should I do if I only see totals instead of the individual expiration dates?
In the Group by dialog, choose All Rows or Text.Combine for text values. If you choose Sum or Count, Power Query aggregates the values and you will not see the individual dates or quantities.
Can WPS Office replace Power BI?
WPS Office does not have a direct Power BI equivalent, but it can open, clean, and save Excel or CSV source files for Power BI import, and it offers a free, lightweight Microsoft Office-compatible spreadsheet tool.
