Power Query to Transform Header

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Column1Column2Column3Column4Column5Column6
NameIDBegin YearEnd YearSubMain
null02420152016null0128
GLAccountAmountDebit-CreditBeginning-Ending
10011070000003$500DE
12345660000004$100CB
NameIDBegin YearEnd YearSubMain
null02420192020null0130
65432190000001$200CB
98765480000002$300DE

In Power Query the main headers for this table is in bold (black color) followed by values underneath it, but there are sub-headers in red which I would like to transform in column format so that the Begin Year, End Year and Main goes down a column until the next sub-header is found. How can I make this transformation?
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
no, it doesn't
so show proper FULL expected result

Post # 3 is the full expected result. I don't know what else to show. Post # 1 shows the example and Post #3 shows the output I want to achieve. If you take Post #3 then look at Post #1 you can see how those three sub-header columns (Begin Year, End Year, and Main) align with each transaction. The main column headers is in bold but I also need those three fields (I'm referring to them as sub-headers) from above it to be included in a tabular form going down for each transaction until the next sub-header is found.

So Begin Year 2015 End Year 2016 and Main 0128 belongs only to the two transactions below it and Begin Year 2019 End Year 2020 and Main 0130 belongs only to the last two transactions below it.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
is that correct?
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
2015201612810011070000003$500DE
2015201612865432190000001$200CB
2019202013098765480000002$300DE
2019202013012345660000004$100CB
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
ignore previous post
here is a proper table (I hope)
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
2015201612810011070000003$500DE
2015201612812345660000004$100CB
2019202013065432190000001$200CB
2019202013098765480000002$300DE
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
ignore previous post
here is a proper table (I hope)
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
2015201612810011070000003$500DE
2015201612812345660000004$100CB
2019202013065432190000001$200CB
2019202013098765480000002$300DE
Yes! That's it. Will just need the number formatting to show leading zero for Main. Can you please provide code. Thx.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

Power Query:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if [Column1] = "Name" then 0 else if [Column1] = null then 1 else 2),
    Sort0 = Table.Sort(IF,{{"Custom", Order.Ascending}}),
    RC0 = Table.RemoveColumns(Sort0,{"Custom"}),
    FirstN = Table.FirstN(RC0,4),
    Skip0 = Table.Skip(FirstN,1),
    Promote0 = Table.PromoteHeaders(Skip0, [PromoteAllScalars=true]),
    TSC = Table.SelectColumns(Promote0,{"Begin Year", "End Year", "Main"}),
    Skip1 = Table.Skip(RC0,4),
    Sort1 = Table.Sort(Skip1,{{"Column1", Order.Descending}}),
    RC1 = Table.RemoveColumns(Sort1,{"Column6"}),
    Promote1 = Table.PromoteHeaders(RC1, [PromoteAllScalars=true]),
    Tables = Table.AddColumn(Promote1, "Tables", each TSC),
    Expand = Table.ExpandTableColumn(Tables, "Tables", {"Begin Year", "End Year", "Main"}, {"Begin Year", "End Year", "Main"}),
    Sort2 = Table.Sort(Expand,{{"Begin Year", Order.Ascending}, {"GL", Order.Ascending}}),
    AR = Table.AlternateRows(Sort2,2,4,2),
    Reorder = Table.ReorderColumns(AR,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    Reorder
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
20152016012810011070000003$500DE
20152016012812345660000004$100CB
20192020013065432190000001$200CB
20192020013098765480000002$300DE
 
Last edited:

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Power Query:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if [Column1] = "Name" then 0 else if [Column1] = null then 1 else 2),
    Sort0 = Table.Sort(IF,{{"Custom", Order.Ascending}}),
    RC0 = Table.RemoveColumns(Sort0,{"Custom"}),
    FirstN = Table.FirstN(RC0,4),
    Skip0 = Table.Skip(FirstN,1),
    Promote0 = Table.PromoteHeaders(Skip0, [PromoteAllScalars=true]),
    TSC = Table.SelectColumns(Promote0,{"Begin Year", "End Year", "Main"}),
    Skip1 = Table.Skip(RC0,4),
    Sort1 = Table.Sort(Skip1,{{"Column1", Order.Descending}}),
    RC1 = Table.RemoveColumns(Sort1,{"Column6"}),
    Promote1 = Table.PromoteHeaders(RC1, [PromoteAllScalars=true]),
    Tables = Table.AddColumn(Promote1, "Tables", each TSC),
    Expand = Table.ExpandTableColumn(Tables, "Tables", {"Begin Year", "End Year", "Main"}, {"Begin Year", "End Year", "Main"}),
    Sort2 = Table.Sort(Expand,{{"Begin Year", Order.Ascending}, {"GL", Order.Ascending}}),
    AR = Table.AlternateRows(Sort2,2,4,2),
    Reorder = Table.ReorderColumns(AR,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    Reorder
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
20152016012810011070000003$500DE
20152016012812345660000004$100CB
20192020013065432190000001$200CB
20192020013098765480000002$300DE
Did you use the table in post #1 as your Table1 source? I'm getting some errors. Can you provide me your source table so I know I'm not missing anything. Thanks.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

Column1Column2Column3Column4Column5Column6Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
NameIDBegin YearEnd YearSubMain20152016012810011070000003$500DE
2420152016012820152016012812345660000004$100CB
GLAccountAmountDebit-CreditBeginning-Ending20192020013065432190000001$200CB
10011070000003$500DE20192020013098765480000002$300DE
12345660000004$100CB
NameIDBegin YearEnd YearSubMain
24201920200130
65432190000001$200CB
98765480000002$300DE
 

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
147
Office Version
  1. 365
Platform
  1. Windows
Please try


Power Query:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedYear = Table.AddColumn(Source, "T", each if Value.Is([Column6], type number) then _ else null),
    Expanded = Table.ExpandRecordColumn(AddedYear, "T", {"Column3", "Column4", "Column6"}, {"Column3.1", "Column4.1", "Column6.1"}),
    FilledDown = Table.FillDown(Expanded,{"Column3.1", "Column4.1", "Column6.1"}),
    RemovedTopRows = Table.Skip(FilledDown,2),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars=true]),
    Filtered = Table.SelectRows(PromotedHeaders, each ([Column6] = null)),
    RenamedColumns = Table.RenameColumns(Filtered, List.Zip({List.LastN(Table.ColumnNames(Filtered),3), {"Begin Year","End Year","Main"}})),
    SelectColumns = Table.SelectColumns(RenamedColumns,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    SelectColumns
 
Solution

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.Group(
                        Source,
                        "Column1",
                        {
                            "n",
                            each let
                                    a=Table.ToRows(_),
                                    b=List.RemoveRange(List.LastN(a{1},4),2,1),
                                    c=List.FirstN(a{2},5),
                                    d=List.Transform(List.Skip(a,(m)=>m{0} is text),(s)=>b&List.FirstN(s,5))
                                in
                                    #table({"Begin Year","End Year","Main","GL","Account","Amount","Debit-Credit","Beginning-End"},d)
                        },
                        0,
                        (x,y)=>
                                Byte.From(y="Name")
                     )
in
    Table.Combine(res[n])
工作簿1.xlsx
ABCDEFGH
1Column1Column2Column3Column4Column5Column6
2NameIDBegin YearEnd YearSubMain
3null2420152016null128
4GLAccountAmountDebit-CreditBeginning-Ending
510011070000003$500DE
612345660000004$100CB
7NameIDBegin YearEnd YearSubMain
8null2420192020null130
965432190000001$200CB
1098765480000002$300DE
11
12Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-End
132015201612810011070000003500DE
142015201612812345660000004100CB
152019202013065432190000001200CB
162019202013098765480000002300DE
Sheet1
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Column1Column2Column3Column4Column5Column6Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-Ending
NameIDBegin YearEnd YearSubMain20152016012810011070000003$500DE
2420152016012820152016012812345660000004$100CB
GLAccountAmountDebit-CreditBeginning-Ending20192020013065432190000001$200CB
10011070000003$500DE20192020013098765480000002$300DE
12345660000004$100CB
NameIDBegin YearEnd YearSubMain
24201920200130
65432190000001$200CB
98765480000002$300DE
The "AR" step where you hard coded the Remove Alternate Rows is not going to work because I provided just a sample data that is easy to work with. My real data has a lot of transactions and it can vary each time my source table changes. This step needs to be dynamic.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,633
Messages
5,626,002
Members
416,150
Latest member
Dawzzy

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