Formatting data to use in pivot table

[omard.]

Hello All,

I have this issue have been trying to solve for a while now and cant seem to find any solution. I'm hoping someone here can help me.

Here is the issues. I have data that comes to me in this format and it have a few more columns and rows.

Dept Users
Finance I cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault
Finance I Discretionary
Finance II cn=User2,ou=users,o=vault
Finance II Discretionary
Finance III cn=User3,ou=users,o=vault
Finance III Discretionary
Finance IV cn=User4,ou=users,o=vault
Finance IV Discretionary
Finance V cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault

I first do a text to column to get all the users out of one cell and that is where i get stumped.

What i need it to look is like this.

Dept Users
Finance I cn=User0,ou=users,o=vault
Finance I cn=User1,ou=users,o=vault
Finance I Discretionary
Finance II cn=User2,ou=users,o=vault
Finance II Discretionary
Finance III cn=User3,ou=users,o=vault
Finance III Discretionary
Finance IV cn=User4,ou=users,o=vault
Finance IV Discretionary
Finance V cn=User5,ou=users,o=vault
Finance V cn=User6,ou=users,o=vault
Finance V cn=User7,ou=users,o=vault
Finance V cn=User8,ou=users,o=vault

The user information gets place vertically instead of horizontally and all the information in the cells leading up to the users gets duplicated down.

Any help would be so appreciated.

 

[=ODIN=]

A little more info might help here.

Can you say which database exported the file this way? Also what is the file extension of the file producing this format -- .csv, etc ?

 

[omard.]

The information comes out of an Identity Management system with very poor reporting. A query is run and that output is copy and pasted in a .csv file. I really hope this helps

 

[sandy666]

with PowerQuery (Get&Transform)

Dept Users		Dept	Users
Finance I cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault		Finance I	cn=User0,ou=users,o=vault
Finance I Discretionary		Finance I	cn=User1,ou=users,o=vault
Finance II cn=User2,ou=users,o=vault		Finance I	Discretionary
Finance II Discretionary		Finance II	cn=User2,ou=users,o=vault
Finance III cn=User3,ou=users,o=vault		Finance II	Discretionary
Finance III Discretionary		Finance III	cn=User3,ou=users,o=vault
Finance IV cn=User4,ou=users,o=vault		Finance III	Discretionary
Finance IV Discretionary		Finance IV	cn=User4,ou=users,o=vault
Finance V cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault		Finance IV	Discretionary
		Finance V	cn=User5,ou=users,o=vault
		Finance V	cn=User6,ou=users,o=vault
		Finance V	cn=User7,ou=users,o=vault
		Finance V	cn=User8,ou=users,o=vault

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Dept Users", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Dept Users.1", "Dept Users.2", "Dept Users.3"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Dept Users.3", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Dept Users.3"),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter1",{"Dept Users.1", "Dept Users.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Dept"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Dept Users.3", "Users"}})
in
    #"Renamed Columns"[/SIZE]

 

[sandy666]

or if there are two columns it will be much simpler:

Dept	Users		Dept	Users
Finance I	cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault		Finance I	cn=User0,ou=users,o=vault
Finance I	Discretionary		Finance I	cn=User1,ou=users,o=vault
Finance II	cn=User2,ou=users,o=vault		Finance I	Discretionary
Finance II	Discretionary		Finance II	cn=User2,ou=users,o=vault
Finance III	cn=User3,ou=users,o=vault		Finance II	Discretionary
Finance III	Discretionary		Finance III	cn=User3,ou=users,o=vault
Finance IV	cn=User4,ou=users,o=vault		Finance III	Discretionary
Finance IV	Discretionary		Finance IV	cn=User4,ou=users,o=vault
Finance V	cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault		Finance IV	Discretionary
			Finance V	cn=User5,ou=users,o=vault
			Finance V	cn=User6,ou=users,o=vault
			Finance V	cn=User7,ou=users,o=vault
			Finance V	cn=User8,ou=users,o=vault

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Users", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Users")
in
    #"Split Column by Delimiter"[/SIZE]

 

[omard.]

What a life saver. Thank you so much.

 

[sandy666]

You are welcome

Have a nice day