Split/restructure and fill a column based names

[nyman90]

Hello,

This is my first thread so ill hope you have some understanding

Anyways, I have this issue which I don't manage to solve. I've obtained some information in a spread sheet and it is structured like this:
[Column 1] Column 2
[Information 1] 1,000
[Information 2] 2,000
[Information 3] 3,000
[Company 1] 6,000
[Information 1] 2,000
[Information 2] 3,000
[Information 3] 4,000
[Company 2] 9,000

etc.

I want to restructure this into the following but I have no idea if its even possible:
[Column 1] [Columns 2] Column 3
[Company 1] [Information 1] 1,000
[Company 1] [Information 2] 2,000
[Company 1] [Information 3] 3,000
[Company 2] [Information 1] 2,000
[Company 2] [Information 2] 3,000
[Company 2] [Information 3] 4,000

Thanks,
Philip

 

[nyman90]

FYI - Im trying to do this in the Power Query Editor

 

[horseyride]

Probably a more compact way to do this, but this works

Add an index
Add custom column that uses Mod function and index to create a second index that just keeps repeating 1,2,3,0
Filter out the 0's which is Company, then duplicate each row 4 times, and add another index
Go back to original table and this time keep only the 1,2,3's then merge against the new Company table index to pick out company name

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],4)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <>0 )),

    #"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each ([Custom] = 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Custom2", each {1..4}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom2"),
    Table2 = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 1, 1),

    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Index"},Table2,{"Index.1"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Column1"}, {"Column1.1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table2",{"Column1.1", "Column2", "Column1"})
in
    #"Removed Other Columns"

 

[horseyride]

This might be more compact, does without the merging

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],4)),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom.1", each #"Added Custom1"{[Index]+3-[Custom]}[Column1]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> 0)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom.1", "Column2", "Column1"})
in #"Removed Other Columns"

 

[nyman90]

Probably a more compact way to do this, but this works

Add an index
Add custom column that uses Mod function and index to create a second index that just keeps repeating 1,2,3,0
Filter out the 0's which is Company, then duplicate each row 4 times, and add another index
Go back to original table and this time keep only the 1,2,3's then merge against the new Company table index to pick out company name

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],4)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <>0 )),

    #"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each ([Custom] = 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Custom2", each {1..4}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom2"),
    Table2 = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 1, 1),

    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Index"},Table2,{"Index.1"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Column1"}, {"Column1.1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table2",{"Column1.1", "Column2", "Column1"})
in
    #"Removed Other Columns"

Thank you but what if the number of information item varies between the different companies. For example Company 1 has 5 information items and Company 2 has 7 information items.

Best,
PN

 

[horseyride]

It could be done with variable rows, but how do you distinguish a row with a company name from a row with info in it? You sample data does not tell

 

[nyman90]

So for example, this is what I have:

Building - 5,000
Land - 2,300
Company - 1 7,300
Building - 1,000
Land - 5,000
Equipment - 2,000
Company 2 - 8,000

And then this goes on for a couple of hundred times haha. I could also add that the Company names varies alot.

 

[horseyride]

Still doesn't provide the needed info. What distinguishes the rows that have the company name on them from other row?
Do the rows with company name on them actually start with the word "Company" ? If so, then below would work
Otherwise, modify the "Added Custom" row to put in the formula for what distinguishes them for your data

let Source =Table.FromRows({{"Building", 5000} , {"Land",2300}, {"Company1",7300},{"Building", 1000} , {"Land",5000},{"Equipment",5000}, {"Company2",8000}}, {"Column1", "Column2"}),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([Column1],7)="Company" then [Column1] else null),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Custom", "Custom.2"),
#"Filled Up" = Table.FillUp(#"Duplicated Column",{"Custom.2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] =null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in #"Removed Columns"

 

[peter789]

Sorry if this doesn't help but could I suggest a couple of solutions?
1. Is it possible to obtain a master list of Company names from the originator of the spread sheet? Then merge the two queries to create a flag indicating which are the company names. Various options then exist for manipulating the data.
2. The second method I can visualise but the exact form of the code escapes me. Step one, add an index. Step two sort by index in descending order, the first row must now contain a Company name. Step three possibly using List.Generate subtract the value in the second row from the value in the previous row and store the result, then repeat until the stored value is zero. The next row must then be a company so flag the row. Reset the loop and continue.