Cleaning data for analysis.

[ronaldbalza2023]

I'm struggling to format this data for analysis. Could you please provide steps or a solution using Power Query? Thanks in advance 🙂

https://www.dropbox.com/scl/fi/bvczqbvutlo4c2q6xf5kr/Hotel-X-STR-2307.xls?rlkey=cfn4c3ghne1rmzum8mbt...

[lbendlin]

- Avoid using .xls files if you can - they require legacy drivers.  Use .xlsx

- avoid using data sources with merged cells or embedded logos

- Will the file always have three table ranges or is that flexible?

- Will the data always be for a single month?

[ronaldbalza2023]

Hi @lbendlin , thanks for taking the time looking at this.

- Avoid using .xls files if you can - they require legacy drivers.  Use .xlsx - Noted on this 🙂

- Will the file always have three table ranges or is that flexible? - yes, it is always a three table ranges.

- Will the data always be for a single month? - yes, it is a single month. 

[lbendlin]

Can you please post the xlsx version?

[ronaldbalza2023]

@lbendlin here it is 🙂 https://www.dropbox.com/scl/fi/htktna04tftgf7hu25x39/Hotel-X-STR-2307.xlsx?rlkey=9vfrkzif3zbg6ifpwba...

[lbendlin · Accepted solution]

Thank you.  Please note this Data Quality Issue:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Hotel X STR 2307.xlsx"), null, true),
    Daily_Sheet = Source{[Item="Daily",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Daily_Sheet,8),
    #"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each [Column1] <> null and [Column1] <> "Period" and not Text.StartsWith([Column1],"Total")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1", "Column4", "Column5", "Column12", "Column13", "Column20", "Column22"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Column1"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each 
if [Attribute]="Column4" then "Occupancy|My Prop"
else if [Attribute]="Column5" then "Occupancy|Comp Set"
else if [Attribute]="Column12" then "ADR|My Prop"
else if [Attribute]="Column13" then "ADR|Comp Set"
else if [Attribute]="Column20" then "RevPAR|My Prop"
else if [Attribute]="Column22" then "RevPAR|Comp Set"
else [Attribute]
,Replacer.ReplaceValue,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "Date"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Item", "Measure"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date", type date}, {"Value", type number}})
in
    #"Changed Type"

[ronaldbalza2023]

Thank you very much @lbendlin , that worked 🙂