Convert Rowed Data into Columned Data

Power Query
1

Hi all,

I have a specific task to convert multiple persons responsible for hundreds of Cost Centers from row format into column format.

As can be seen in the raw format being in rows, one Cost Center can have multiple persons.

image

The desired result is to have each unique cost center on row and different persons in columns.

image

I have been googling but still cannot solve this.

Could anyone please help suggest a solution? Thank you and much appreciated.Cost Center Person.xlsx (9.7 KB)

2

@kbi
Looks like you can use the hierarchy functions for this. Is that what you are trying to get to? If not, I dont see a reason to change the data from how you have it since it is in tabular form and that’s what works best and easiest with DAX and Power Query.

Thanks,

Enterprise%20DNA%20Expert%20-%20Small

3

Hi Nick,

Thanks for reply.

I need to convert the Persons Responsible into columns as I need to distribute this file to the business to fill in the persons for each cost center. It would make more sense for them to go by unique cost center and fill in the persons horizontally.

Plus I would need to look up these unique cost centers into a hierarchy before distributing to the business areas. That way they can identify their cost center areas more easily.

The returned file will be input into my Row-Level Security roles.

That’s why I really need the file in columnar format.

4

@kbi
See the file attached. I broke out all the steps in Powery Query so you could see them, but you could really combine a few of those. This wasnt a hierarchy issue, but basically:

  1. Group my Cost Center and Aggregate by All Data
  2. New Column to select the “Person Responsible” column from each sub-table from #1
  3. New column to convert #2 into a list
  4. New column using List.Accumulate
    List.Accumulate(
    [Custom.1],“”,(state,current) => state&" || "& current)

( you can do #4 in 1 step )

  1. Remove the misc columns you dont need (or may not even be there if you combined steps 2 to 4)

  2. Split the column from #4 by the delimiter used, but only on the left most this time. Then remove that column

  3. Split the remaining column by the delimiter, but at every occurence.

  4. TRIM the new columns

  5. Set data types, and you should be good

Final%20Table Cost Center Person v2.xlsx (107.7 KB)

Nick,

Enterprise%20DNA%20Expert%20-%20Small

1 Like
5

This is just awesome! Thanks Nick.