Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | ######## | ######## | ######## | ######## | ######## | ######## |
Solved! Go to 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.
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |