Forum Discussion
Add/Append column header (title) to new row
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.
Community SupportHi 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