Transform Multiple Row Data Into A Single Row Help Please!

HI,

I will appreciate if you could help me to Transform Multiple Row Data Into A Single Row. I thought I had an idea but it didn’t work correctly. I am trying to combine both files, removing duplicate row 1 data as file contains the same data for Row 1.

The columns which I need are:

Date d’opération
Date de valeur
Libellé
Montant

Column Libelle is where I try to transform multiple row data into one single row. Is there any chance to do it correctly?. Maybe grouping by Index? I grouped by Montant but I shouldn’t done as data might contain the same amount but it will be for different transaction description for different date. Also Is it possible to remove a space between words?

I will appreciate your help.

I really not sure why you want to transform this into one row?

This to me is setup perfectly for the start of any analysis in Power BI. Yes you may need to remove duplicate but that isn’t too difficult, just do that in the query editor with one click…

For the spreadsheet data, maybe there is some other cleaning up, but again this is setup as it should be for Power BI, so bit confused here about what you are attempting to do.

Have you had a chance to go through the advanced modeling course at Enterprise DNA Online. This cover all my best practise tips for managing data.

See below.

Maybe I’m missing something here, but I would recommend not over complicating things here if you don’t need to.

Sam

Hi, thank you for reply.

I would like to transform multiple rows into one row for each transaction as it would be easier for me to go through each transaction. I want to build reconciliation report in Power BI thats why I need to have each transaction description (Column M “Libelle”) in one row instead of multiple rows.

As the data does not have anything unique, would be possible to somehow group the description into one text line?

@sam.mckay,

Could you possibly explain the technique you used to generate the data in your first screenshot? I worked on a way to get just the column Libelle values into a single value, but I found that a critical missing piece was a unique group ID per set of rows that represent one transaction.

For this to be easy, the unique ID has to come in from the data source (Excel) or be created in the Query Editor. My technique for the unique group ID worked but was only in DAX and would not allow the other required steps to be performed in the Query Editor.

It looks from your screenshot like you got the concatenation to work for Libelle across multiple rows while also pulling the values from the other desired columns. Inquiring minds want to know :smiley:

Thanks!

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small

HI John,

Sorry the Power BI table image is what already existed in the pbix file, I didn’t do anything.

But I understand a bit better now the problem, but yes running into the same problem as yourself with no corresponding ID in the data to match up the information.

I’m interested to know how you got there. I’ve got part way there. But not all the way.

I thought that you could break them out into the details and transaction, then bring them back together with a common ID.

The transaction table is easy.

Just getting rid of the null values, then adding a simple index.

Then I played around with a number of options for the details and got to here

I was hoping this would let me switch it round but not quite there.

Hoping we can all collaborate on this one to get a final solution that could work.

Transform Multiple Row Data Into A Single Row (1).pbix (40.1 KB)

Thanks
Sam

2 Likes

Hi everyone,

I feel Sam’s done most of the heavy lifting already, all I did was sync the Updated Index between the Transactions and the Details queries, that way they now have a shared key. Then Grouped and Combined the Libellé and Merged that with the Transactions.

So this is what Transactions looks like after the Merge,:

If this needs to be done for multiple files, you’ll either need to append them all first so the Index lines up OR add the file name in a column so you can use that as another key.

eDNA - Transform Multiple Row Data Into A Single Row.pbix (64.6 KB)

3 Likes

I will just add my bit for the potential use of a DAX method of determining a grouping ID across multiple transaction rows. I had started with the original data files because my Power BI Report Server version of Desktop is not compatible with the Premium/Hosted PBIX files.

To generate a unique transaction ID for each variable number of rows based on releve[Code Enregistrement] = “4” or “5”, I first added a standard Index column to the table using the Query Editor. This assigns a unique number to each row. I then applied the changes and went back to the data table editor.

Second, I added a calculated TrxID column generated with DAX (screenshot below). The DAX handles the header and footer rows by assigning the current [Index] value as the TrxID. For the other rows, the DAX captures the [Index] value for the last occurrence of releve[Code Enregistrement] = “4” and applies that as the TrxID value until another instance of “4” is encountered.

It looks like @sam.mckay and @Melissa have it all figured out using the Query Editor and then the awesome ETL functions in Power BI, so we are all the better for it!

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small

3 Likes

Thank you for all your help @sam.mckay @jpratt @Melissa

@sam.mckay Would you be able to share the advanced editor or the file so I could have a look and try to understand all the magic behind.

The file which you have uploaded is the original one.

@jpratt I like your solution it helps a lot :), it’s working but I need to have it in Query editor :slight_smile:

Thank you !

I will appreciate if you could respond :slight_smile:

Hi Matty,

Transform Multiple Row Data Into A Single Row (1).pbix (89.6 KB)