Power Query - Unstack data

Mathexcel

New Member
Joined
Jun 22, 2017
Messages
32
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 NameDateValuePercentage
Property1Tue 07 Jan 2020
67​
-0.0944722​
Property1Wed 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 NameAttributeValue
Property1Tue 07 Jan 2020
67​
Property1Column5
-0.09447​
Property1Wed 08 Jan 2020
67​
Property1Column7
-0.06932​
Property2Thu 09 Jan 2020
59​
Property2Column9
-0.06349​
Property2Fri 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

Well-known Member
Joined
Oct 24, 2015
Messages
3,746
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
Joined
Oct 24, 2015
Messages
3,746
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
Joined
Sep 9, 2016
Messages
38
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

New Member
Joined
Sep 9, 2016
Messages
38
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

New Member
Joined
Jun 22, 2017
Messages
32
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!

MonthAccronymMonth#
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

New Member
Joined
Sep 9, 2016
Messages
38
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!

MonthAccronymMonth#
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

New Member
Joined
Sep 9, 2016
Messages
38
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"
 

Forum statistics

Threads
1,081,642
Messages
5,360,211
Members
400,578
Latest member
ajayanand1717

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top