cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BICrazy
Helper I
Helper I

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?

CampaignStatusGeneral ManagerJunior ManagerEmployee NumberAgent Name1 March 20211 March 20211 March 20212 March 20212 March 20212 March 2021
Existing/NewPackagesPremiumUnlimitedPackagesPremiumUnlimited
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx703803
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx500300
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx302502
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx500602
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx000503
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx200400



 

 

CampaignStatusGeneral ManagerJunior ManagerEmployee NumberAgent Name1 April 20211 April 20211 April 20216 April 20216 April 20216 April 2021
Existing/NewPackagesPremiumUnlimitedPackagesPremiumUnlimited
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx101000
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx505606
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx606303
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx202000
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx404404
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx000808



Regards

 

1 ACCEPTED SOLUTION

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"

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

7 REPLIES 7
watkinnc
Responsive Resident
Responsive Resident

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

Payeras_BI
Super User I
Super User I

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

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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"

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

@Payeras_BI  Thanks a mill, this worked perfectly.  Will remember this going forward.

Payeras_BI
Super User I
Super User I

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.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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:

BICrazy_1-1618925021958.png

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors