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.
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.
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
