Manipulating a Flat File (Moving Columns under other Columns)

[GiveMeYourBeans]

I want to thank everyone in advance. This has been driving me crazy and I'm hoping that there is a solution.

Below is a very small sample of a flat file I'm hoping to manipulate. As you can see there are 2 users, creating 2 rows. Each "user" can have multiple locations and each location corresponds to a billing address. For example Location 1 Name corresponds with Remit 1 Name.

Last Name	First Name	Location 1 Name	Location 1 Address 1	Remit 1 Name	Remit 1 Address 1	Location 2 Name	Location 2 Address 1	Remit 2 Name	Remit 2 Address 1
Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road

My question being is it possible to manipulate the flat file so that "Location Name 2" will appear below "Location Name 1" and so on. To make matters more complicated is it possible to have "Remit 2 Name" below Remit 1 Name"?

I'm specifically talking about the data in the columns, not the column headers themselves.

My hope is that it can be manipulated to look like this:

Last Name	First Name	Location 1 Name	Location 1 Address 1	Remit 1 Name	Remit 1 Address 1
Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
Smith	John	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road

The issue is that for this project a flat file is required because of some of the other data that is excluded from this example. This becomes a very messy file for the simple fact that a spreadsheet could have 1000 "Users" with them having anywhere from 1-20 locations & remit combinations. Any help would be GREATLY appreciated!

 

[GiveMeYourBeans]

I would go with the code I posted in Post #5. But, below is a slight amendment to the code in that post that should account for how many columns you will need. Meaning that you don't need to hard code that number in. Hopefully the code should just work.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], List.Count(List.Distinct(Index[Attribute]))), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt

I'm getting the error "5 arguments were passed to function which expects between 2 and 4."

I would go with the code I posted in Post #5. But, below is a slight amendment to the code in that post that should account for how many columns you will need. Meaning that you don't need to hard code that number in. Hopefully the code should just work.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], List.Count(List.Distinct(Index[Attribute]))), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt

I'm getting the error "5 arguments were passed to function which expects between 2 and 4" for the Index." I'm going to work on putting together a full file, hopefully that'll make the query a little easier.

 

[GiveMeYourBeans]

I would go with the code I posted in Post #5. But, below is a slight amendment to the code in that post that should account for how many columns you will need. Meaning that you don't need to hard code that number in. Hopefully the code should just work.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], List.Count(List.Distinct(Index[Attribute]))), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt

I'm getting the error "5 arguments were passed to function which expects between 2 and 4" for the Index. I'm going to put together a full file example. Perhaps this will make it more clear for me when you help with the code.