Add/Append column header (title) to new row

[Mike_Allen_MU]

My apologies is this has already been answered.  I tried looking but could not find anything close enough to get me past where I am stuck.  I also pre-apologize in case my question is clear as mud. 

My goal is to append or add a variable number of column names (or headers) to the rows so I can get the full accounting string for entry posting.  Below is an image, where we have the funding source across the columns and the rest of the accounting information is in columns B&C in each row.  So the goal is to end up with fund/org(columnB)/account(ColumnC)/account name(columnD)/amount.

Original forms look like this and there are multiple tabs across multiple files like this.  And the number of Columns with fund numbers is variable and can be anywhere between 1 and 5 columns, although they always start in Column E.  So basically the forms are all the same except the width will vary based on the number of funds used.  

Desired output would look something like this:

Going to post a CSV version of one tab at the bottomr of the message in case that is helpful.  Any help pointing me the in right direction would be greatly appreciated.  

Thanks in advance,

Mike

			Budget Unit:	BU05					College of Whatever
			Org:	2500					Department ABC
			Fund:	119014	200000	199000	701101	309101	Prior Year
			Return to Summary by Org					Expenditures
			Sources of funding
BU05	2500	5T0010	5T0010 Operating Allocation	########
BU05	2500	5TDR10	5TDR10 Transfer from Dept Reserve	$-	########
BU05	2500	5T0050	5T0050 Internal - Unit Temp Transfer	$-		########
BU05	2500	5T0051	5T0051 Internal-Unit Personal Services Trf	$-			########
BU05	2500	5T0055	5T0055 Unit to Unit Temp Transfer	$-				########
BU05	2500	536710	536710 State Grants & Contracts	########	########	########	########	########
BU05	2500	546710	546710 Local Grants & Contracts	########	########	########	########	########
BU05	2500	556702	556702 Private Contracts	$-	########
BU05	2500	556710	556710 Private Grants	$-		########
BU05	2500	596966	596966 Miscellaneous - General	$-			########
BU05	2500	5R0510	5R0510 Reimbursement - Miscelleanous	$-				########
BU05	2500			$-
BU05	2500			$-
BU05	2500			$-
BU05	2500			$-
BU05	2500			$-
			Total Sources	########	########	########	########	########	$-
			Please increase sources or decrease expenditures to balance.
			Temporary Labor
BU05	2500	60015	Graduate Assistants	$-	########				$-
BU05	2500	60016	Student Assistants	$-		########			$-
BU05	2500	60019	Work Study	$-			########		$-
BU05	2500	60017	Part Time Faculty	$-				########	$-
BU05	2500	60018	Casual Help	$-	########				$-
BU05	2500	60023	Classified Temporary	$-		########			$-
BU05	2500	6001C2	Faculty Summer Term	$-			########		$-
BU05	2500	6001F	Overtime	$-				########	$-
BU05	2500	6001R	Faculty Research	$-					$-
BU05	2500	6001S	Stipends	########					########
BU05	2500	6001T	Cell Phone Stipend	$-					$660.00
BU05	2500	6001U	Internet Stipend	$-					$180.00
BU05	2500	6999B	Benefits Temporary Labor	$-					########
			Total Temporary Labor	########	########	########	########	########	########
			Direct Expenditures
BU05	2500	70024	Computers	$-					$-
BU05	2500	70024	Contractual Services	$-					$-
BU05	2500	70024	Deferred Maintenance	$-					$-
BU05	2500	70024	Equipment	$-					$-
BU05	2500	70024	Functional Travel	$-					$-
BU05	2500	70024	Information Technology	$-					$-
BU05	2500	70024	Lab Expenses	$-					$-
BU05	2500	70024	Memberships & Accreditations	$-					$300.00
BU05	2500	70024	MURC Positions	$-					$-
BU05	2500	70024	Office Expense	$-					########
BU05	2500	70024	Other Functional Expense	$-					########
BU05	2500	70024	Renovations & Repairs	$-					$-
BU05	2500	70024	Research Expenses	$-					$-
BU05	2500	70024	Scholarships	$-					$-
BU05	2500	70024	Software	$-					$-
BU05	2500	70024	Student Activities	$-					$-
BU05	2500	70024	Training & Development	########					$-
BU05	2500	70021	Utilities	$-					$-
BU05	2500	70009	Budget Future Commitment	$-					$-
BU05	2500	71531	Debt Service Principal	$-					$-
BU05	2500	71541	Interest on Debt Service	$-					$-
BU05	2500	70280	Inter-Agency Agreement-Indirect Cost	$-					$-
			Total Direct Expenditures	########	$-	$-	$-	$-	########
			Total Uses	########	########	########	########	########	########
			Net Total - Sources less Uses	########	########	########	########	########	########

[Icey · Accepted solution]

Hi @Mike_Allen_MU ,

Is this what you want?

let
    Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\Mike.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column10"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column4] <> "" and [Column4] <> "Budget Unit: " and [Column4] <> "Org: ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"_1", type text}, {"Fund:", type text}, {"119014", type text}, {"200000", type text}, {"199000", type text}, {"701101", type text}, {"309101", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([_1] <> "")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"", "_1", "Fund:"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Fund"}, {"Value", "Amount"}, {"Fund:", "Account Name"}, {"_1", "Account"}, {"", "Org"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Fund", "Org", "Account", "Account Name", "Amount"})
in
    #"Reordered Columns"

For more details, check the attachments.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mike_Allen_MU]

Icey,

Thanks again for all your assistance.  I think I will mark this as complete, as you answered the question in reguards to a single tab which is very useful and informative.  However I need to have the appended funding information be able to change from worksheet to worksheet (in the original excel files).  Thanks to your help, I think I am better able to ask my question.  So I will make this as complete and start a new thread for doing this same query across multiple pages in an excel spreadsheet and post a pbix example.

Thanks so much for your assistance Icey!!

Mike

[Mike_Allen_MU]

Thank you for your reply and assistance Icey, you are very kind.  Your solution is almost exactly what I need.  However I need the column names (funds) to be dynamic as they are often different on every sheet.  So instead of hard coding 

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"_1", type text}, {"Fund:", type text}, {"119014", type text}, {"200000", type text}, {"199000", type text}, {"701101", type text}, {"309101", type text}})

If the 119014, 200000, 199000, 701101, 309101 could just refence the column number (aka 5,6,7,8,9) or something like that, then it would work for any range of funds and fit all sheets with a maximum of 5 funding sources (IE 5 columns).  

Thanks again for all your help and effort, it is greatly appreciated,

Mike

[Icey]

Hi @Mike_Allen_MU ,

How about changing types in the last step like so:

let
    Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\Mike.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column10"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column4] <> "" and [Column4] <> "Budget Unit: " and [Column4] <> "Org: ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([_1] <> "")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"", "_1", "Fund:"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Fund"}, {"Value", "Amount"}, {"Fund:", "Account Name"}, {"_1", "Account"}, {"", "Org"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Fund", "Org", "Account", "Account Name", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Fund", Int64.Type}, {"Org", Int64.Type}, {"Account", type text}, {"Account Name", type text}, {"Amount", type text}})
in
    #"Changed Type"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.