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 the March dates at the unpivot stage. Is there a way to get the dates from each table to display accurately when appending these tables and then unpivoting the dates?CampaignStatusGeneral ManagerJunior ManagerEmployee NumberAgent Name1 March 20211 March 20211 March 20212 March 20212 March 20212 March 2021Existing/NewPackagesPremiumUnlimitedPackagesPremiumUnlimitedxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx703803xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx500300xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx302502xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx500602xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx000503xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx200400 CampaignStatusGeneral ManagerJunior ManagerEmployee NumberAgent Name1 April 20211 April 20211 April 20216 April 20216 April 20216 April 2021Existing/NewPackagesPremiumUnlimitedPackagesPremiumUnlimitedxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx101000xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx505606xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx606303xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx202000xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx404404xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx000808Regards

Helper II

5 years ago

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 the March dates at the unpivot stage. Is there a way to get the dates from each table to display accurately when appending these tables and then unpivoting the dates?

Campaign	Status	General Manager	Junior Manager	Employee Number	Agent Name	1 March 2021	1 March 2021	1 March 2021	2 March 2021	2 March 2021	2 March 2021
Existing/New	Packages	Premium	Unlimited	Packages	Premium	Unlimited
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	7	0	3	8	0	3
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	5	0	0	3	0	0
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	3	0	2	5	0	2
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	5	0	0	6	0	2
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	0	0	0	5	0	3
xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	xxxxxxxxxxxx	2	0	0	4	0	0

Campaign	Status	General Manager	Junior Manager	Employee Number	Agent Name	1 April 2021	1 April 2021	1 April 2021	6 April 2021	6 April 2021	6 April 2021
Existing/New	Packages	Premium	Unlimited	Packages	Premium	Unlimited
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	1	0	1	0	0	0
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	5	0	5	6	0	6
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	6	0	6	3	0	3
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	2	0	2	0	0	0
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	4	0	4	4	0	4
xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	xxxxxxxxx	0	0	0	8	0	8

Regards

Need Help

Payeras_BI

5 years ago

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"

7 Replies

Payeras_BI
Solution Sage
5 years ago
Hi BICrazy ,

Are you using the option to Get Data from a Folder in Power Query?

If so you can apply the Unpivoting on the Sample File Query, before they get merged.
- BICrazy
  Helper II
  5 years ago
  Hi Payeras, yes, that is exactly how I am loading the data. March is my set as my sample file where I applied the unpivoting, but when I load April data I get the error:

Payeras_BI

Solution Sage

5 years ago

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

BICrazy

Helper II

5 years ago

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

Payeras_BI

Solution Sage

5 years ago

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"

Anonymous
Not applicable
5 years ago
You can grab the fresh column names inside of the unpivot step: just replace the Column list with List.SkipN(Table.ColumnNames(_)), 6)
That will give you the list of columns to unpivot, skipping over the first 6 columns.
--Nate

Forum Discussion

Append date columns

7 Replies

Related content

Dataflow - Append

Append Date column

Find Most Recent Date Relative to another Date Column in Power Query

Appending Data

Progressive Date Columns