Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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
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.
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.
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: