Categories in Rows as column

[andiart]

Hi,

I have an excel export from our ticket system. It is sorted by category. This category stands as a leading row before a bunch of real data rows. So, where the id is, there sometimes is the category for the next x rows. Like that:

ID            | Title          |       Requestor

----------------------------------------

Emailserver|null|null....

1             | No Mail    | Erwin

2             | Plain text  | Janina

Citrix|null|null....

3             | no login..  | John

......

So I'd like to have the categories as a column for all successing row until the new category and so on.

I know it is to accomplish, because I have seen it in a video, but I forgot how, because I'm completely new to power bi,

Anyone can hint?

Andreas

[andiart]

Hi,

in the data transform window under "Transform column" there are those icons:

Use "Bedingte Spalte" , which means conditional column

It results in the following code for this step:

= Table.AddColumn(#"Umbenannte Spalten2", "Kategorie", each if [Anforderungsmodus] = null then [#"Kat"] else null)

The fill down of the category is in the transform tab, it is called "Ausfüllen" in german:

You can choose the direction down in this step.

[andiart · Accepted solution]

I found a solution:

1. added a conditional column "Category" with "null value in second row" as a condition

2. "fill down" in new column

3. filter second column not showing rows with "null" value

[jianni]

Is there any way you can provide the exact steps as I am a beginner? I selected "Conditional Column" in the Add Column menu but it does not allow me to add the condition "null value in second row". Also, how do I "fill down" in the new column? TIA!

[andiart]

So in the first step I copy the category to a column of its own, because in my case it stood in the first colum, where are real values as well. I used a conditional column, with a condition to copy only the value from the first column if a certain other column is null, which is only the case, when it is a category column, because in real value lines there are values in that column, as the category stands alone in its row.

The Fill down in the newly created column just copies the category value into each row beneath until a new category value appears, where it does the same copying with that value. So each row has the corresponding category. I hope that helps you.