Manipulating a Flat File (Moving Columns under other Columns)

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1Location 2 NameLocation 2 Address 1Remit 2 NameRemit 2 Address 1
DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnApple Tree Road123 RoadBilling Group435 Greentree RoadHollywood Sports325 Pennsylvania AveJoes Billing435 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 NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1
DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnHollywood Sports325 Pennsylvania AveJoes Billing435 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

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

Power Query:
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.

Power Query:
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

Power Query:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top