How to group data based on header

[learningpython]

Hi Experts,

Note: This question was asked in Power Query: Grouping Data based on Color of the font or other ways - Microsoft Q&A Unfortunately they do not support power query anymore. so I have come back to my usual place of Excel to see if you support power query and delighted to see have a power query group.

Please can you help me with this. I have some .pdf presented to me and I have only Office 16 power query so I cannot read .PDF so I copied and pasted them in excel
This is what I want

The raw data in pdf has a Header with 10s of listings under each Header and about 10000 headers.
I have only office 16 professional and cannot read PDF so I copied it to a .xls file.

Example (sorry silly one!)
Purchase Order
Customer
Bill
receipt

Address
Door / Apt #
Street
City
State
Zip

etc long list

Now when I extract to .xls using copy and paste, I get the font but my challenge is the following

I need to Create a Power Pivot for each of the Header so I get the elements associated.
Example: When I select " Purchase Order ", I must get a listing of
"Customer
Bill
receipt "


one way is to add a column with a flash fill of associated Header but How can I do it in Power Query based on Font ?
Or any other best ways you advise would be appreciated.

 

[alansidman]

We cannot manipulate data in pictures. Please reload your data using XL2BB. See my signature for instructions.

 

[learningpython]

Hi Experts,

Note: This question was asked in Power Query: Grouping Data based on Color of the font or other ways - Microsoft Q&A Unfortunately they do not support power query anymore. so I have come back to my usual place of Excel to see if you support power query and delighted to see have a power query group.

Please can you help me with this. I have some .pdf presented to me and I have only Office 16 power query so I cannot read .PDF so I copied and pasted them in excel
This is what I want

The raw data in pdf has a Header with 10s of listings under each Header and about 10000 headers.
I have only office 16 professional and cannot read PDF so I copied it to a .xls file.

Example (sorry silly one!)
Purchase Order
Customer
Bill
receipt

Address
Door / Apt #
Street
City
State
Zip

etc long list

Now when I extract to .xls using copy and paste, I get the font but my challenge is the following

I need to Create a Power Pivot for each of the Header so I get the elements associated.
Example: When I select " Purchase Order ", I must get a listing of
"Customer
Bill
receipt "


one way is to add a column with a flash fill of associated Header but How can I do it in Power Query based on Font ?
Or any other best ways you advise would be appreciated.

We cannot manipulate data in pictures. Please reload your data using XL2BB. See my signature for instructions.

GroupingwithaFONT_OR_Color.xlsx
	A	B	C	D
1	Input
2	Purchase Order	Note: Header could be different font or color
3	Customer
4	Bill
5	receipt
6	Address	Note: Header could be different font or color
7	Door / Apt #
8	Street
9	City
10	State
11	Zip
12
13		Output Expected Example 1	Output Expected Example 2	etc
14		Purchase Order	Address
15		Customer	Door / Apt #
16		Bill	Street
17		receipt	City
18			State
19			Zip
20
Input

Apologies, Doing this first time, I hope I have followed instructions

 

[learningpython]

@alansidman Please may I know if you can see the sheet which I have attached based on your instructions using xl2bb​

 

[alansidman]

I can see your data. Power Query does not recognize color nor formatting. I have no idea how to help you with the current layout and your expected results. Good Luck finding a solution. If you change your expected results then post back and I will relook at if there is a possible PQ solution. I would also urge you to show us 6-8 records with expected results. One record is not sufficient.

 

[JGordon11]

You need a way to identify the headers such as a custom function:

Function IsHeader(rng As Range) As Boolean
    If rng.Font.Bold Or rng.Font.Size > 10 Then IsHeader = True
End Function

Then you can transform a table like the blue one below into the green one using the M code below:

Book7
	A	B	C	D	E	F	G
1	Column1	Column2			Purchase Order	Address
2	Purchase Order	TRUE			Customer	Door / Apt #
3	Customer	FALSE			Bill	Street
4	Bill	FALSE			receipt	City
5	receipt	FALSE				State
6	Address	TRUE				Zip
7	Door / Apt #	FALSE
8	Street	FALSE
9	City	FALSE
10	State	FALSE
11	Zip	FALSE
12
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IsHeader(A2)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tblType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type logical}}),
    tbl1 = Table.AddColumn(tblType, "Headers", each if [Column2] = true then [Column1] else null),
    tbl2 = Table.FillDown(tbl1,{"Headers"}),
    tbl3 = Table.SelectRows(tbl2, each ([Column2] = false)),
    headers = List.Distinct(tbl3[Headers]),
    lstCols = List.Generate(()=>0, (x)=> x< List.Count(headers), (x)=> x+1, (x)=> Table.SelectRows(tbl3, each _[Headers] = headers{x})[Column1]),
    tbl4 = Table.FromColumns(lstCols,headers)
in
    tbl4