# Power Query - Unstack data

#### Mathexcel

##### New Member
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?

Matthieu

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### sandy666

##### Well-known Member
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

Last edited:

#### sandy666

##### Well-known Member
something like this?

#### sandy666

##### Well-known Member
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

##### New Member
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),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "column", Comparer.OrdinalIgnoreCase))
in
#"Filtered Rows"``````

#### bkjohn2016

##### New Member
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}}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Custom] <> null and not Text.Contains([Attribute], "column", Comparer.OrdinalIgnoreCase))
in
#"Filtered Rows"``````

#### Mathexcel

##### New Member
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)),
#"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),
#"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}}),
#"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

##### New Member
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)),
#"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),
#"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}}),
#"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

##### New Member
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],
#"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"``````