Power Query - Unstack data

Mathexcel · Jan 13, 2020

Hi MrExcel Community,

I am facing a new unstacking data challenge. ?

Context
I have a bunch of Property Names, two Attributes which are stacked together in the following order: (1) a date (in a text format) and (2) a percentage change and their Values.

Target
Here is that table format I am aiming to get:

Property Name	Date	Value	Percentage
Property1	Tue 07 Jan 2020	67	-0.0944722
Property1	Wed 08 Jan 2020	67	-0.0693152

> I am thinking to add an index column to indicate if it is a Date or a Percentage, but don't have the skills to execute this query (yet).

Table Source
Here is a sample of the data source:

Property Name	Attribute	Value
Property1	Tue 07 Jan 2020	67
Property1	Column5	-0.09447
Property1	Wed 08 Jan 2020	67
Property1	Column7	-0.06932
Property2	Thu 09 Jan 2020	59
Property2	Column9	-0.06349
Property2	Fri 10 Jan 2020	59

Would anyone know how to transform this piece of data into the desired format?

I appreciate your precious time. ?

Matthieu

sandy666 · Jan 13, 2020

your percentage column is a sum (or whatever) of decimal values? or source and result are incompatible with data

maybe post example AND expected result from this example

sandy666 · Jan 13, 2020

something like this?

Mathexcel · Jan 13, 2020

sandy666 said:
something like this?
View attachment 3851

That's what I am looking for!!

sandy666 · Jan 13, 2020

Code:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Condition = Table.AddColumn(Source, "Percent", each if Text.Contains([Attribute], "Column") then [Value] else null),
    FU = Table.FillUp(Condition,{"Percent"}),
    FD = Table.FillDown(FU,{"Percent"}),
    Filter = Table.SelectRows(FD, each ([Attribute] <> "Column5" and [Attribute] <> "Column7" and [Attribute] <> "Column9")),
    Group = Table.Group(Filter, {"Property Name", "Value", "Percent"}, {{"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Group, "Count", {"Attribute"}, {"Attribute"}),
    ROC = Table.SelectColumns(Expand,{"Property Name", "Attribute", "Value", "Percent"})
in
    ROC

bkjohn2016 · Jan 14, 2020

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property Name", type text}, {"Attribute", type text}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Attribute],"Column") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "column", Comparer.OrdinalIgnoreCase))
in
    #"Filtered Rows"

bkjohn2016 · Jan 14, 2020

Or using index:

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property Name", type text}, {"Attribute", type text}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), "Custom", each try Source[Value]{[Index]+1} otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Custom] <> null and not Text.Contains([Attribute], "column", Comparer.OrdinalIgnoreCase))
in
    #"Filtered Rows"

Mathexcel · Jan 14, 2020

Amazing, it works! Many thanks!

I split the attribute column and then created a Month table so I could merge it in order to get my dates in the right format!

MonthAccronym	Month#
Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

I was wondering if there are ways to optimise my code so it runs faster. The bold part is what I have added with your solution.

Rich (BB code):

let
    Source = Folder.Files("C:\Users"),
    #"Filtered Rows" = Table.SelectRows(Source, let earliest = List.Min(Source[Date modified]) in each [Date modified] = earliest),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (7)", each #"Transform File (7)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (7)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (7)", Table.ColumnNames(#"Transform File (7)"(#"Sample File (7)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Best Flexible Rate - My Hotels LOS 1, Booking.com", type text}, {"Column3", type any}, {"Column4", type number}, {"Column5", type any}, {"Column6", type number}, {"Column7", type any}, {"Column8", type number}, {"Column9", type any}, {"Column10", type number}, {"Column11", type any}, {"Column12", type number}, {"Column13", type any}, {"Column14", type number}, {"Column15", type any}, {"Column16", type number}, {"Column17", type any}, {"Column18", type number}, {"Column19", type any}, {"Column20", type number}, {"Column21", type any}, {"Column22", type number}, {"Column23", type any}, {"Column24", type number}, {"Column25", type any}, {"Column26", type number}, {"Column27", type any}, {"Column28", type number}, {"Column29", type any}, {"Column30", type number}, {"Column31", type any}, {"Column32", type number}, {"Column33", type any}, {"Column34", type number}, {"Column35", type any}, {"Column36", type number}, {"Column37", type any}, {"Column38", type number}, {"Column39", type any}, {"Column40", type number}, {"Column41", type any}, {"Column42", type number}, {"Column43", type any}, {"Column44", type number}, {"Column45", type any}, {"Column46", type number}, {"Column47", type any}, {"Column48", type number}, {"Column49", type any}, {"Column50", type number}, {"Column51", type any}, {"Column52", type number}, {"Column53", type any}, {"Column54", type number}, {"Column55", type any}, {"Column56", type number}, {"Column57", type any}, {"Column58", type number}, {"Column59", type any}, {"Column60", type number}, {"Column61", type any}, {"Column62", type number}, {"Column63", type any}, {"Column64", type number}, {"Column65", type any}, {"Column66", type number}, {"Column67", type any}, {"Column68", type number}, {"Column69", type any}, {"Column70", type number}, {"Column71", type any}, {"Column72", type number}, {"Column73", type any}, {"Column74", type number}, {"Column75", type any}, {"Column76", type number}, {"Column77", type any}, {"Column78", type number}, {"Column79", type any}, {"Column80", type number}, {"Column81", type any}, {"Column82", type number}, {"Column83", type any}, {"Column84", type number}, {"Column85", type any}, {"Column86", type number}, {"Column87", type any}, {"Column88", type number}, {"Column89", type any}, {"Column90", type number}, {"Column91", type any}, {"Column92", type number}, {"Column93", type any}, {"Column94", type number}, {"Column95", type any}, {"Column96", type number}, {"Column97", type any}, {"Column98", type number}, {"Column99", type any}, {"Column100", type number}, {"Column101", type any}, {"Column102", type number}, {"Column103", type any}, {"Column104", type number}, {"Column105", type any}, {"Column106", type number}, {"Column107", type any}, {"Column108", type number}, {"Column109", type any}, {"Column110", type number}, {"Column111", type any}, {"Column112", type number}, {"Column113", type any}, {"Column114", type number}, {"Column115", type any}, {"Column116", type number}, {"Column117", type any}, {"Column118", type number}, {"Column119", type any}, {"Column120", type number}, {"Column121", type any}, {"Column122", type number}, {"Column123", type any}, {"Column124", type number}, {"Column125", type any}, {"Column126", type number}, {"Column127", type any}, {"Column128", type number}, {"Column129", type any}, {"Column130", type number}, {"Column131", type any}, {"Column132", type number}, {"Column133", type any}, {"Column134", type number}, {"Column135", type any}, {"Column136", type number}, {"Column137", type any}, {"Column138", type number}, {"Column139", type any}, {"Column140", type number}, {"Column141", type any}, {"Column142", type number}, {"Column143", type any}, {"Column144", type number}, {"Column145", type any}, {"Column146", type number}, {"Column147", type any}, {"Column148", type number}, {"Column149", type any}, {"Column150", type number}, {"Column151", type any}, {"Column152", type number}, {"Column153", type any}, {"Column154", type number}, {"Column155", type any}, {"Column156", type number}, {"Column157", type any}, {"Column158", type number}, {"Column159", type any}, {"Column160", type number}, {"Column161", type any}, {"Column162", type number}, {"Column163", type any}, {"Column164", type number}, {"Column165", type any}, {"Column166", type number}, {"Column167", type any}, {"Column168", type number}, {"Column169", type any}, {"Column170", type number}, {"Column171", type any}, {"Column172", type number}, {"Column173", type any}, {"Column174", type number}, {"Column175", type any}, {"Column176", type number}, {"Column177", type any}, {"Column178", type number}, {"Column179", type any}, {"Column180", type number}, {"Column181", type any}, {"Column182", type number}, {"Column183", type any}, {"Column184", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column1"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Best Flexible Rate - My Hotels LOS 1"}, Portfolio, {"OTA Insights"}, "Portfolio", JoinKind.LeftOuter),
    #"Expanded Portfolio_AMH" = Table.ExpandTableColumn(#"Merged Queries", "Portfolio", {"Hotel Code", "Ideas Name"}, {"Hotel Code", "Ideas Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Portfolio",{"Hotel Code", "Ideas Name", "Best Flexible Rate - My Hotels LOS 1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Column3] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Tue 07 Jan 2020", type any}, {"Column5", type number}, {"Wed 08 Jan 2020", type any}, {"Column7", type number}, {"Thu 09 Jan 2020", type any}, {"Column9", type number}, {"Fri 10 Jan 2020", type any}, {"Column11", type number}, {"Sat 11 Jan 2020", type any}, {"Column13", type number}, {"Sun 12 Jan 2020", type any}, {"Column15", type number}, {"Mon 13 Jan 2020", type any}, {"Column17", type number}, {"Tue 14 Jan 2020", type any}, {"Column19", type number}, {"Wed 15 Jan 2020", type any}, {"Column21", type number}, {"Thu 16 Jan 2020", type any}, {"Column23", type number}, {"Fri 17 Jan 2020", type any}, {"Column25", type number}, {"Sat 18 Jan 2020", type any}, {"Column27", type number}, {"Sun 19 Jan 2020", type any}, {"Column29", type number}, {"Mon 20 Jan 2020", type any}, {"Column31", type number}, {"Tue 21 Jan 2020", type any}, {"Column33", type number}, {"Wed 22 Jan 2020", type any}, {"Column35", type number}, {"Thu 23 Jan 2020", type any}, {"Column37", type number}, {"Fri 24 Jan 2020", type any}, {"Column39", type number}, {"Sat 25 Jan 2020", type any}, {"Column41", type number}, {"Sun 26 Jan 2020", type any}, {"Column43", type number}, {"Mon 27 Jan 2020", type any}, {"Column45", type number}, {"Tue 28 Jan 2020", type any}, {"Column47", type number}, {"Wed 29 Jan 2020", type any}, {"Column49", type number}, {"Thu 30 Jan 2020", type any}, {"Column51", type number}, {"Fri 31 Jan 2020", type any}, {"Column53", type number}, {"Sat 01 Feb 2020", type any}, {"Column55", type number}, {"Sun 02 Feb 2020", type any}, {"Column57", type number}, {"Mon 03 Feb 2020", type any}, {"Column59", type number}, {"Tue 04 Feb 2020", type any}, {"Column61", type number}, {"Wed 05 Feb 2020", type any}, {"Column63", type number}, {"Thu 06 Feb 2020", type any}, {"Column65", type number}, {"Fri 07 Feb 2020", type any}, {"Column67", type number}, {"Sat 08 Feb 2020", type any}, {"Column69", type number}, {"Sun 09 Feb 2020", type any}, {"Column71", type number}, {"Mon 10 Feb 2020", type any}, {"Column73", type number}, {"Tue 11 Feb 2020", type any}, {"Column75", type number}, {"Wed 12 Feb 2020", type any}, {"Column77", type number}, {"Thu 13 Feb 2020", type any}, {"Column79", type number}, {"Fri 14 Feb 2020", type any}, {"Column81", type number}, {"Sat 15 Feb 2020", type any}, {"Column83", type number}, {"Sun 16 Feb 2020", type any}, {"Column85", type number}, {"Mon 17 Feb 2020", type any}, {"Column87", type number}, {"Tue 18 Feb 2020", type any}, {"Column89", type number}, {"Wed 19 Feb 2020", type any}, {"Column91", type number}, {"Thu 20 Feb 2020", type any}, {"Column93", type number}, {"Fri 21 Feb 2020", type any}, {"Column95", type number}, {"Sat 22 Feb 2020", type any}, {"Column97", type number}, {"Sun 23 Feb 2020", type any}, {"Column99", type number}, {"Mon 24 Feb 2020", type any}, {"Column101", type number}, {"Tue 25 Feb 2020", type any}, {"Column103", type number}, {"Wed 26 Feb 2020", type any}, {"Column105", type number}, {"Thu 27 Feb 2020", type any}, {"Column107", type number}, {"Fri 28 Feb 2020", type any}, {"Column109", type number}, {"Sat 29 Feb 2020", type any}, {"Column111", type number}, {"Sun 01 Mar 2020", type any}, {"Column113", type number}, {"Mon 02 Mar 2020", type any}, {"Column115", type number}, {"Tue 03 Mar 2020", type any}, {"Column117", type number}, {"Wed 04 Mar 2020", type any}, {"Column119", type number}, {"Thu 05 Mar 2020", type any}, {"Column121", type number}, {"Fri 06 Mar 2020", type any}, {"Column123", type number}, {"Sat 07 Mar 2020", type any}, {"Column125", type number}, {"Sun 08 Mar 2020", type any}, {"Column127", type number}, {"Mon 09 Mar 2020", type any}, {"Column129", type number}, {"Tue 10 Mar 2020", type any}, {"Column131", type number}, {"Wed 11 Mar 2020", type any}, {"Column133", type number}, {"Thu 12 Mar 2020", type any}, {"Column135", type number}, {"Fri 13 Mar 2020", type any}, {"Column137", type number}, {"Sat 14 Mar 2020", type any}, {"Column139", type number}, {"Sun 15 Mar 2020", type any}, {"Column141", type number}, {"Mon 16 Mar 2020", type any}, {"Column143", type number}, {"Tue 17 Mar 2020", type any}, {"Column145", type number}, {"Wed 18 Mar 2020", type any}, {"Column147", type number}, {"Thu 19 Mar 2020", type any}, {"Column149", type number}, {"Fri 20 Mar 2020", type any}, {"Column151", type number}, {"Sat 21 Mar 2020", type any}, {"Column153", type number}, {"Sun 22 Mar 2020", type any}, {"Column155", type number}, {"Mon 23 Mar 2020", type any}, {"Column157", type number}, {"Tue 24 Mar 2020", type any}, {"Column159", type number}, {"Wed 25 Mar 2020", type any}, {"Column161", type number}, {"Thu 26 Mar 2020", type any}, {"Column163", type number}, {"Fri 27 Mar 2020", type any}, {"Column165", type number}, {"Sat 28 Mar 2020", type any}, {"Column167", type number}, {"Sun 29 Mar 2020", type any}, {"Column169", type number}, {"Mon 30 Mar 2020", type any}, {"Column171", type number}, {"Tue 31 Mar 2020", type any}, {"Column173", type number}, {"Wed 01 Apr 2020", type any}, {"Column175", type number}, {"Thu 02 Apr 2020", type any}, {"Column177", type number}, {"Fri 03 Apr 2020", type any}, {"Column179", type number}, {"Sat 04 Apr 2020", type any}, {"Column181", type number}, {"Sun 05 Apr 2020", type any}, {"Column183", type number}, {"Mon 06 Apr 2020", type any}, {"Column185", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Hotel Code"}, {"Column2", "Hotel Name"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([Hotel Name] <> null)),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows2", {"Hotel Code", "Hotel Name", "Column3"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Column3", "Hotel Code"}),

    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Percent", each if Text.Contains([Attribute], "Column") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Percent"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "Column")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows3", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
    #"Merged Queries1" = Table.NestedJoin(#"Split Column by Delimiter", {"Attribute.3"}, Months, {"MonthAccronym"}, "Months", JoinKind.LeftOuter),
    #"Expanded Months" = Table.ExpandTableColumn(#"Merged Queries1", "Months", {"Month#"}, {"Month#"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Months",{{"Month#", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Date", each Text.Combine({[Attribute.2],[#"Month#"],[Attribute.4]},"/")),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Month#", "Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Hotel Name", "Date", "Value", "Percent"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Percent", Percentage.Type}})
in
    #"Changed Type4"

bkjohn2016 · Jan 14, 2020

Mathexcel said:

Amazing, it works! Many thanks!

I split the attribute column and then created a Month table so I could merge it in order to get my dates in the right format!

MonthAccronym	Month#
Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

I was wondering if there are ways to optimise my code so it runs faster. The bold part is what I have added with your solution.

Rich (BB code):

let
    Source = Folder.Files("C:\Users"),
    #"Filtered Rows" = Table.SelectRows(Source, let earliest = List.Min(Source[Date modified]) in each [Date modified] = earliest),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (7)", each #"Transform File (7)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (7)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (7)", Table.ColumnNames(#"Transform File (7)"(#"Sample File (7)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Best Flexible Rate - My Hotels LOS 1, Booking.com", type text}, {"Column3", type any}, {"Column4", type number}, {"Column5", type any}, {"Column6", type number}, {"Column7", type any}, {"Column8", type number}, {"Column9", type any}, {"Column10", type number}, {"Column11", type any}, {"Column12", type number}, {"Column13", type any}, {"Column14", type number}, {"Column15", type any}, {"Column16", type number}, {"Column17", type any}, {"Column18", type number}, {"Column19", type any}, {"Column20", type number}, {"Column21", type any}, {"Column22", type number}, {"Column23", type any}, {"Column24", type number}, {"Column25", type any}, {"Column26", type number}, {"Column27", type any}, {"Column28", type number}, {"Column29", type any}, {"Column30", type number}, {"Column31", type any}, {"Column32", type number}, {"Column33", type any}, {"Column34", type number}, {"Column35", type any}, {"Column36", type number}, {"Column37", type any}, {"Column38", type number}, {"Column39", type any}, {"Column40", type number}, {"Column41", type any}, {"Column42", type number}, {"Column43", type any}, {"Column44", type number}, {"Column45", type any}, {"Column46", type number}, {"Column47", type any}, {"Column48", type number}, {"Column49", type any}, {"Column50", type number}, {"Column51", type any}, {"Column52", type number}, {"Column53", type any}, {"Column54", type number}, {"Column55", type any}, {"Column56", type number}, {"Column57", type any}, {"Column58", type number}, {"Column59", type any}, {"Column60", type number}, {"Column61", type any}, {"Column62", type number}, {"Column63", type any}, {"Column64", type number}, {"Column65", type any}, {"Column66", type number}, {"Column67", type any}, {"Column68", type number}, {"Column69", type any}, {"Column70", type number}, {"Column71", type any}, {"Column72", type number}, {"Column73", type any}, {"Column74", type number}, {"Column75", type any}, {"Column76", type number}, {"Column77", type any}, {"Column78", type number}, {"Column79", type any}, {"Column80", type number}, {"Column81", type any}, {"Column82", type number}, {"Column83", type any}, {"Column84", type number}, {"Column85", type any}, {"Column86", type number}, {"Column87", type any}, {"Column88", type number}, {"Column89", type any}, {"Column90", type number}, {"Column91", type any}, {"Column92", type number}, {"Column93", type any}, {"Column94", type number}, {"Column95", type any}, {"Column96", type number}, {"Column97", type any}, {"Column98", type number}, {"Column99", type any}, {"Column100", type number}, {"Column101", type any}, {"Column102", type number}, {"Column103", type any}, {"Column104", type number}, {"Column105", type any}, {"Column106", type number}, {"Column107", type any}, {"Column108", type number}, {"Column109", type any}, {"Column110", type number}, {"Column111", type any}, {"Column112", type number}, {"Column113", type any}, {"Column114", type number}, {"Column115", type any}, {"Column116", type number}, {"Column117", type any}, {"Column118", type number}, {"Column119", type any}, {"Column120", type number}, {"Column121", type any}, {"Column122", type number}, {"Column123", type any}, {"Column124", type number}, {"Column125", type any}, {"Column126", type number}, {"Column127", type any}, {"Column128", type number}, {"Column129", type any}, {"Column130", type number}, {"Column131", type any}, {"Column132", type number}, {"Column133", type any}, {"Column134", type number}, {"Column135", type any}, {"Column136", type number}, {"Column137", type any}, {"Column138", type number}, {"Column139", type any}, {"Column140", type number}, {"Column141", type any}, {"Column142", type number}, {"Column143", type any}, {"Column144", type number}, {"Column145", type any}, {"Column146", type number}, {"Column147", type any}, {"Column148", type number}, {"Column149", type any}, {"Column150", type number}, {"Column151", type any}, {"Column152", type number}, {"Column153", type any}, {"Column154", type number}, {"Column155", type any}, {"Column156", type number}, {"Column157", type any}, {"Column158", type number}, {"Column159", type any}, {"Column160", type number}, {"Column161", type any}, {"Column162", type number}, {"Column163", type any}, {"Column164", type number}, {"Column165", type any}, {"Column166", type number}, {"Column167", type any}, {"Column168", type number}, {"Column169", type any}, {"Column170", type number}, {"Column171", type any}, {"Column172", type number}, {"Column173", type any}, {"Column174", type number}, {"Column175", type any}, {"Column176", type number}, {"Column177", type any}, {"Column178", type number}, {"Column179", type any}, {"Column180", type number}, {"Column181", type any}, {"Column182", type number}, {"Column183", type any}, {"Column184", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column1"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Best Flexible Rate - My Hotels LOS 1"}, Portfolio, {"OTA Insights"}, "Portfolio", JoinKind.LeftOuter),
    #"Expanded Portfolio_AMH" = Table.ExpandTableColumn(#"Merged Queries", "Portfolio", {"Hotel Code", "Ideas Name"}, {"Hotel Code", "Ideas Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Portfolio",{"Hotel Code", "Ideas Name", "Best Flexible Rate - My Hotels LOS 1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Column3] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Tue 07 Jan 2020", type any}, {"Column5", type number}, {"Wed 08 Jan 2020", type any}, {"Column7", type number}, {"Thu 09 Jan 2020", type any}, {"Column9", type number}, {"Fri 10 Jan 2020", type any}, {"Column11", type number}, {"Sat 11 Jan 2020", type any}, {"Column13", type number}, {"Sun 12 Jan 2020", type any}, {"Column15", type number}, {"Mon 13 Jan 2020", type any}, {"Column17", type number}, {"Tue 14 Jan 2020", type any}, {"Column19", type number}, {"Wed 15 Jan 2020", type any}, {"Column21", type number}, {"Thu 16 Jan 2020", type any}, {"Column23", type number}, {"Fri 17 Jan 2020", type any}, {"Column25", type number}, {"Sat 18 Jan 2020", type any}, {"Column27", type number}, {"Sun 19 Jan 2020", type any}, {"Column29", type number}, {"Mon 20 Jan 2020", type any}, {"Column31", type number}, {"Tue 21 Jan 2020", type any}, {"Column33", type number}, {"Wed 22 Jan 2020", type any}, {"Column35", type number}, {"Thu 23 Jan 2020", type any}, {"Column37", type number}, {"Fri 24 Jan 2020", type any}, {"Column39", type number}, {"Sat 25 Jan 2020", type any}, {"Column41", type number}, {"Sun 26 Jan 2020", type any}, {"Column43", type number}, {"Mon 27 Jan 2020", type any}, {"Column45", type number}, {"Tue 28 Jan 2020", type any}, {"Column47", type number}, {"Wed 29 Jan 2020", type any}, {"Column49", type number}, {"Thu 30 Jan 2020", type any}, {"Column51", type number}, {"Fri 31 Jan 2020", type any}, {"Column53", type number}, {"Sat 01 Feb 2020", type any}, {"Column55", type number}, {"Sun 02 Feb 2020", type any}, {"Column57", type number}, {"Mon 03 Feb 2020", type any}, {"Column59", type number}, {"Tue 04 Feb 2020", type any}, {"Column61", type number}, {"Wed 05 Feb 2020", type any}, {"Column63", type number}, {"Thu 06 Feb 2020", type any}, {"Column65", type number}, {"Fri 07 Feb 2020", type any}, {"Column67", type number}, {"Sat 08 Feb 2020", type any}, {"Column69", type number}, {"Sun 09 Feb 2020", type any}, {"Column71", type number}, {"Mon 10 Feb 2020", type any}, {"Column73", type number}, {"Tue 11 Feb 2020", type any}, {"Column75", type number}, {"Wed 12 Feb 2020", type any}, {"Column77", type number}, {"Thu 13 Feb 2020", type any}, {"Column79", type number}, {"Fri 14 Feb 2020", type any}, {"Column81", type number}, {"Sat 15 Feb 2020", type any}, {"Column83", type number}, {"Sun 16 Feb 2020", type any}, {"Column85", type number}, {"Mon 17 Feb 2020", type any}, {"Column87", type number}, {"Tue 18 Feb 2020", type any}, {"Column89", type number}, {"Wed 19 Feb 2020", type any}, {"Column91", type number}, {"Thu 20 Feb 2020", type any}, {"Column93", type number}, {"Fri 21 Feb 2020", type any}, {"Column95", type number}, {"Sat 22 Feb 2020", type any}, {"Column97", type number}, {"Sun 23 Feb 2020", type any}, {"Column99", type number}, {"Mon 24 Feb 2020", type any}, {"Column101", type number}, {"Tue 25 Feb 2020", type any}, {"Column103", type number}, {"Wed 26 Feb 2020", type any}, {"Column105", type number}, {"Thu 27 Feb 2020", type any}, {"Column107", type number}, {"Fri 28 Feb 2020", type any}, {"Column109", type number}, {"Sat 29 Feb 2020", type any}, {"Column111", type number}, {"Sun 01 Mar 2020", type any}, {"Column113", type number}, {"Mon 02 Mar 2020", type any}, {"Column115", type number}, {"Tue 03 Mar 2020", type any}, {"Column117", type number}, {"Wed 04 Mar 2020", type any}, {"Column119", type number}, {"Thu 05 Mar 2020", type any}, {"Column121", type number}, {"Fri 06 Mar 2020", type any}, {"Column123", type number}, {"Sat 07 Mar 2020", type any}, {"Column125", type number}, {"Sun 08 Mar 2020", type any}, {"Column127", type number}, {"Mon 09 Mar 2020", type any}, {"Column129", type number}, {"Tue 10 Mar 2020", type any}, {"Column131", type number}, {"Wed 11 Mar 2020", type any}, {"Column133", type number}, {"Thu 12 Mar 2020", type any}, {"Column135", type number}, {"Fri 13 Mar 2020", type any}, {"Column137", type number}, {"Sat 14 Mar 2020", type any}, {"Column139", type number}, {"Sun 15 Mar 2020", type any}, {"Column141", type number}, {"Mon 16 Mar 2020", type any}, {"Column143", type number}, {"Tue 17 Mar 2020", type any}, {"Column145", type number}, {"Wed 18 Mar 2020", type any}, {"Column147", type number}, {"Thu 19 Mar 2020", type any}, {"Column149", type number}, {"Fri 20 Mar 2020", type any}, {"Column151", type number}, {"Sat 21 Mar 2020", type any}, {"Column153", type number}, {"Sun 22 Mar 2020", type any}, {"Column155", type number}, {"Mon 23 Mar 2020", type any}, {"Column157", type number}, {"Tue 24 Mar 2020", type any}, {"Column159", type number}, {"Wed 25 Mar 2020", type any}, {"Column161", type number}, {"Thu 26 Mar 2020", type any}, {"Column163", type number}, {"Fri 27 Mar 2020", type any}, {"Column165", type number}, {"Sat 28 Mar 2020", type any}, {"Column167", type number}, {"Sun 29 Mar 2020", type any}, {"Column169", type number}, {"Mon 30 Mar 2020", type any}, {"Column171", type number}, {"Tue 31 Mar 2020", type any}, {"Column173", type number}, {"Wed 01 Apr 2020", type any}, {"Column175", type number}, {"Thu 02 Apr 2020", type any}, {"Column177", type number}, {"Fri 03 Apr 2020", type any}, {"Column179", type number}, {"Sat 04 Apr 2020", type any}, {"Column181", type number}, {"Sun 05 Apr 2020", type any}, {"Column183", type number}, {"Mon 06 Apr 2020", type any}, {"Column185", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Hotel Code"}, {"Column2", "Hotel Name"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([Hotel Name] <> null)),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows2", {"Hotel Code", "Hotel Name", "Column3"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Column3", "Hotel Code"}),

    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Percent", each if Text.Contains([Attribute], "Column") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Percent"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "Column")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows3", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
    #"Merged Queries1" = Table.NestedJoin(#"Split Column by Delimiter", {"Attribute.3"}, Months, {"MonthAccronym"}, "Months", JoinKind.LeftOuter),
    #"Expanded Months" = Table.ExpandTableColumn(#"Merged Queries1", "Months", {"Month#"}, {"Month#"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Months",{{"Month#", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Date", each Text.Combine({[Attribute.2],[#"Month#"],[Attribute.4]},"/")),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Month#", "Attribute.1", "Attribute.2", "Attribute.3", "Attribute.4"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Hotel Name", "Date", "Value", "Percent"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Percent", Percentage.Type}})
in
    #"Changed Type4"

bkjohn2016 · Jan 14, 2020

I'm glad it worked out for you...you should check out the index solution. Understanding row context is very beneficial.

Without seeing the actual dataset, soliciting advice may be a bit difficult; but let's give it a shot!

Right off the bat I'd suggest you leave the date column as it is and forego merging with another table. You should only merge when necessary and it doesn't appear necessary here. Instead change the data type to type date. Create a separate calendar table and then create a relationship between the two tables in the model. You'll need that calendar table to leverage the time intelligence features in DAX. I'll give you one of my calendars if you like.

Something I've learned over time is to recognize and address redundancies and flow. Inspecting your code I notice a series of "Renamed Columns", "Reordered Columns", "Removed Columns", and "Filtered Rows". Ultimately, it comes down to judgement; "Is this step necessary or can it wait until the end?" For instance I try to ensure that the last step in all of my models is always Change Type and that there's only one instance of that function...PQ has a habit of automatically throwing that in there for you. Basically, I try to do all the heavy lifting up front i.e. applying functions, merging, unpivoting, custom columns etc. And once the table resembles something tangible that can provide utility in the data model then start filtering, removing columns, reordering columns, renaming columns, and lastly change type. So, I count 31 steps in the model above...I think you can cut that in half.

Fact Table:

Rich (BB code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Table.AddIndexColumn(Source, "Index", 0, 1), "Custom", each try Source[Value]{[Index]+1} otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and not Text.Contains([Attribute], "column", Comparer.OrdinalIgnoreCase)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Property Name", "Attribute", "Value", "Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Property Name", type text}, {"Attribute", type date}, {"Value", Int64.Type}, {"Custom", type number}})
in
    #"Changed Type"

Calendar Table:

Rich (BB code):

let

//Set variables by getting upper and lower date thresholds from sales table used to calculate range

StartDate=List.Min(Table1[Attribute]),
EndDate=List.Max(Table1[Attribute]),
DateRange=Number.From(EndDate)-Number.From(StartDate)+1,

//Call date function to create calendar base

Source=List.Dates,
DateList=Source(StartDate,DateRange, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
    #"Month-Year" = Table.AddColumn(#"Inserted Day Name", "Month-Year", each Text.From([Year])&"-"&(if [Month]>9 then Text.From([Month]) else "0"&Text.From([Month]))),
    #"Week-Year" = Table.AddColumn(#"Month-Year", "Week-Year", each Text.From([Year])&"-"&(if [Week of Year]>9 then Text.From([Week of Year]) else "0"&Text.From([Week of Year]))),
    #"Year-Qtr" = Table.AddColumn(#"Week-Year", "Quarter-Year", each Text.From([Year])&"-Q"&Text.From([Quarter]), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Year-Qtr",{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Month Name", type text}, {"Quarter", Int64.Type}, {"Week of Year", Int64.Type}, {"Week of Month", Int64.Type}, {"Day", Int64.Type}, {"Day of Week", Int64.Type}, {"Day of Year", Int64.Type}, {"Day Name", type text}, {"Month-Year", type text}, {"Week-Year", type text}})

in
    #"Changed Type"

Power Query - Unstack data

Mathexcel

New Member

Excel Facts

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

Mathexcel

New Member

sandy666

Banned - Rules violations

bkjohn2016

New Member

bkjohn2016

New Member

Mathexcel

New Member

bkjohn2016

New Member

bkjohn2016

New Member

Similar threads

Forum statistics

Share this page

Power Query - Unstack data

New Member

Excel Facts

Banned - Rules violations

Banned - Rules violations

New Member

Banned - Rules violations

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock