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!

 

[lrobbo314]

A way using Power Query.

Book1
	A	B	C	D	E	F	G	H	I	J
1	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
2	Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
3	Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road
4
5	Last Name	First Name	Location Name	Location Address	Remit Name	Remit Address
6	Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
7	Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
8	Smith	John	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4)),
    RC = Table.RemoveColumns(List,{"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"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),
    Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),
    HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))
in
    HH

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Manipulating a Flat File (Moving Columns under other Columns)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[GiveMeYourBeans]

A way using Power Query.

Book1
	A	B	C	D	E	F	G	H	I	J
1	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
2	Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
3	Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road
4
5	Last Name	First Name	Location Name	Location Address	Remit Name	Remit Address
6	Doe	Jane	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
7	Smith	John	Apple Tree Road	123 Road	Billing Group	435 Greentree Road
8	Smith	John	Hollywood Sports	325 Pennsylvania Ave	Joes Billing	435 Greentree Road
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4)),
    RC = Table.RemoveColumns(List,{"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"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),
    Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),
    HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))
in
    HH

Wow! This is exactly what I'm looking for. Is it possible for any additional explanation? I'm trying to follow how you did that.

 

[lrobbo314]

This code is a bit cleaner and probably easier to follow. This one doesn't have a bunch of nested formulas on one line like the last one.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[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], 4), 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

 

[lrobbo314]

As for an explanation of the first PQ code.

List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4))

Record.FieldValues makes a list of all the values on each row. List.Skip skips the last name and first name on that list. I am removing the nulls and then split the lists in increments of 4 since we have 4 columns for each set of address data.

    RC = Table.RemoveColumns(List,{"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"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),

These ones are kind of self explanatory and are done through the GUI, remove columns and expand the column with the lists.

Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

Here I am transforming the column with the lists from a list to a text string with Text.Combine

Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),

This splits the combined text from the previous step into columns.

CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),

I get the original column names from the first step, the original table, and select the columns with Location, Address, Remit Name, and Remit Address. Then I select just the text and spaces, get rid of the numbers, with Text.Select, and also replace the double spaces with single spaces.

HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))

I do the same thing here with getting columns names, but this time working with the SPLIT step. Since when we extracted the list to columns PQ gave the columns names like column1, column2, etc... I am going to rename them. List.Zip combines the 2 lists into the format I need as the argument for Table.RenameColumns.

Hope that helps explain it a bit.

 

[GiveMeYourBeans]

This code is a bit cleaner and probably easier to follow. This one doesn't have a bunch of nested formulas on one line like the last one.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[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], 4), 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 thought a small example of my dataset would help me with the larger dataset. Unfortunately I was wrong. Would you be willing to help me with the full file? I've been running into a wall all weekend.

 

[lrobbo314]

Sure thing.

 

[GiveMeYourBeans]

Sure thing.

First, thank you so much for the help. I can't put into words how helpful this community seems to be.

I'll submit the a full example if we are unable to resolve but I think I have an idea where my major issue is.

I think it is here:
List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4))

In my "real" file there are 101 columns before the location and remit columns. Additionally (and I think this is causing me the most confusion), the "location" columns make up 14 columns and the "remit" locations make up 10 columns. I can't seem to figure out how to adjust the code you forwarded based off this information.

 

[lrobbo314]

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