Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BICrazy
Helper II
Helper II

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Payeras_BI
Super User
Super User

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

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

Payeras_BI
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors