Power Query - Unstack data

M

Mathexcel

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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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:
Upvote 0
something like this?
unst.png
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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"
 
Upvote 0
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"
 
Upvote 0
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!

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"
Click to expand...
 
Upvote 0
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"
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Power Query? Changing Format of Table
Replies
3
Views
176
RoryA
RoryA
T
  • Question
Help converting Power Query that imports data to VBA
Replies
0
Views
293
The Shy Retirer
T
E
  • Question
Dynamic Sheet Name - Power Query
2 3
Replies
23
Views
5K
egoosen3
E
E
  • Question
Unstack data in power query
Replies
2
Views
336
Excel202201
E
J
  • Solved
Use Power Query to Load and Transform Excel File from K Drive
Replies
6
Views
720
Skovgaard
S

Forum statistics

Threads
1,214,424
Messages
6,119,400
Members
448,893
Latest member
AtariBaby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top