Forum Discussion
Solved
Append date columns
Hi there everyone, I'm bringing in monthly spreadsheets from a folder into Power BI. Each monthly table looks like the 2 tables below: I can't get the April dates as they are being renamed to...
Hi BICrazy ,
The issue comes from your #"Changed Type" step where you are hardcoding all those column names ({"3/1/2021", type any}, {"3/1/2021_1", type any}, {"3/1/2021_2", type any}...).
Below the same query without them:
let Source = Excel.Workbook(Parameter1, null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Campaign", type text}, {"Status", type text}, {"General Manager", type text}, {"Junior Manager", type text}, {"Employee Number", Int64.Type}, {"Agent Name", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Campaign] <> null)), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Campaign", "Status", "General Manager", "Junior Manager", "Employee Number", "Agent Name"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number), #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Product Type", each if [Modulo] = 0 then "Package" else if [Modulo] = 1 then "Premium" else if [Modulo] = 2 then "Unlimited" else null), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Attribute.1", "Date"}, {"Value", "Packages"}}) in #"Renamed Columns"
Hi BICrazy ,
What function are you using to Unpivot?
You may want to use "Table.UnpivotOtherColumns" instead of "Table.Unpivot" so you do not need to specify in your query the name of the date columns being unpivoted.
If this still does not help, consider sharing the Transform Sample File query code (Copy/Paste what is in the Advanced Editor).
Hi Payeras,
Definitely using the Table.UnpivotOtherColumns. Below is the advanced editor code. Appreciate the help.
let
Source = Excel.Workbook(Parameter1, null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Campaign", type text}, {"Status", type text}, {"General Manager", type text}, {"Junior Manager", type text}, {"Employee Number", Int64.Type}, {"Agent Name", type text}, {"3/1/2021", type any}, {"3/1/2021_1", type any}, {"3/1/2021_2", type any}, {"3/2/2021", type any}, {"3/2/2021_3", type any}, {"3/2/2021_4", type any}, {"3/3/2021", type any}, {"3/3/2021_5", type any}, {"3/3/2021_6", type any}, {"3/4/2021", type any}, {"3/4/2021_7", type any}, {"3/4/2021_8", type any}, {"3/5/2021", type any}, {"3/5/2021_9", type any}, {"3/5/2021_10", type any}, {"3/8/2021", type any}, {"3/8/2021_11", type any}, {"3/8/2021_12", type any}, {"3/9/2021", type any}, {"3/9/2021_13", type any}, {"3/9/2021_14", type any}, {"3/10/2021", type any}, {"3/10/2021_15", type any}, {"3/10/2021_16", type any}, {"3/11/2021", type any}, {"3/11/2021_17", type any}, {"3/11/2021_18", type any}, {"3/12/2021", type any}, {"3/12/2021_19", type any}, {"3/12/2021_20", type any}, {"3/15/2021", type any}, {"3/15/2021_21", type any}, {"3/15/2021_22", type any}, {"3/16/2021", type any}, {"3/16/2021_23", type any}, {"3/16/2021_24", type any}, {"3/17/2021", type any}, {"3/17/2021_25", type any}, {"3/17/2021_26", type any}, {"3/18/2021", type any}, {"3/18/2021_27", type any}, {"3/18/2021_28", type any}, {"3/19/2021", type any}, {"3/19/2021_29", type any}, {"3/19/2021_30", type any}, {"3/23/2021", type any}, {"3/23/2021_31", type any}, {"3/23/2021_32", type any}, {"3/24/2021", type any}, {"3/24/2021_33", type any}, {"3/24/2021_34", type any}, {"3/25/2021", type any}, {"3/25/2021_35", type any}, {"3/25/2021_36", type any}, {"3/26/2021", type any}, {"3/26/2021_37", type any}, {"3/26/2021_38", type any}, {"3/29/2021", type any}, {"3/29/2021_39", type any}, {"3/29/2021_40", type any}, {"3/30/2021", type any}, {"3/30/2021_41", type any}, {"3/30/2021_42", type any}, {"3/31/2021", type any}, {"3/31/2021_43", type any}, {"3/31/2021_44", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Campaign] <> null)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Campaign", "Status", "General Manager", "Junior Manager", "Employee Number", "Agent Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Product Type", each if [Modulo] = 0 then "Package"
else if [Modulo] = 1 then "Premium"
else if [Modulo] = 2 then "Unlimited"
else null),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Attribute.1", "Date"}, {"Value", "Packages"}})
in
#"Renamed Columns"
Regards
Rakesh
Hi BICrazy ,
The issue comes from your #"Changed Type" step where you are hardcoding all those column names ({"3/1/2021", type any}, {"3/1/2021_1", type any}, {"3/1/2021_2", type any}...).
Below the same query without them:
let Source = Excel.Workbook(Parameter1, null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Campaign", type text}, {"Status", type text}, {"General Manager", type text}, {"Junior Manager", type text}, {"Employee Number", Int64.Type}, {"Agent Name", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Campaign] <> null)), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Campaign", "Status", "General Manager", "Junior Manager", "Employee Number", "Agent Name"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type date}, {"Attribute.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number), #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Product Type", each if [Modulo] = 0 then "Package" else if [Modulo] = 1 then "Premium" else if [Modulo] = 2 then "Unlimited" else null), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Attribute.1", "Date"}, {"Value", "Packages"}}) in #"Renamed Columns"Payeras_BI Thanks a mill, this worked perfectly. Will remember this going forward.