Transaction description in one row

Power Query
1

Transaction sample data.xlsx (18.7 KB)

In my data table there is one column “Transaction Description” the complete description are showing in many rows. I need in one row through query editor.
Please find the attached data sample with highlighted Description column. I will appreciate if someone can do in pbix file and sent back to me.
Thank you.

2

Hi @khurram,

Here’s a great post that describes the process for working through a scenario just like this.

I hope this is helpful.

3

@Melissa This is not a solution. I have checked.

4

@khurram,

Okay. What outcome do you expect?
Please add the expected result to a sheet in your sample file.

5

@Melissa please find the attached data with outcome.

Transaction sample data.xlsx (18.9 KB)

6

Hi @khurram,

See if this works for you.

  • create a group by key
  • group rows
  • combine text strings and keep the top row in the nested table
  • remove helper column and expand nested table

image
image1345×350 17.3 KB

here’s your sample file.
eDNA - Transaction description.pbix (31.4 KB)

I hope this is helpful.

1 Like
7

@Melissa Brilliant!

  • I couldn’t figure it out power query steps from “UpdateIndex to onwards”
  • Have you used the user interface or advanced editor for querying steps?
  • If there is any video available for specific transformation in DNA portal or YouTube, please share a link. It will be more helpful.
    Thank you so much Melissa.
8

Hi @khurram,

Most of the time I’ll use the UI as much as I can and only amend the code when needed.
The logic for creating a group by key is usually a “one off” because that depends on the data and the grouping you want to create but the steps involved are pretty generic:

  1. Add an Index Column
  2. Create conditional logic to return the Index Value on each “first” row and nulls for others
  3. Fill down the values in this conditional column

Now each row “in a group” shares the same key and you can leverage that in a group by step.
Please review the article I referenced in my first response again because that covers the concatination of the text strings pretty well.

I hope this is helpful
All the best

1 Like
9

Hi @khurram, It’s great to know that you are making progress with your query.

However, a gentle reminder that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate.

You can create a separate thread for your other questions related to this inquiry so that other users can easily check the details of your queries.

For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum

We also noticed that no response was seen from you since September 28. Do you still need any help with your question?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

10

Hi @khurram, we’ve noticed that no response has been received from you since September 28.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Hi @khurram, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.