Power Query to combine multiple tables on single sheet into Master Table

[rkaczano]

Hi I am new to Power Query. I need to do the following. I have data that is represented on 1 sheet that is represented as repeating tables that are identical. The data goes left to right (columns) n time (1 to 24) with multiples rows for records. This table then repeats itself for different days in rows. So in effect I have hourly data (columns) per record (rows) per day but the daily data sets are broken into separate repeating tables.

Note the data is publicly available if that helps. I have attached an image of how the data currently looks.

I want to:
1) merge or combine all the separate daily tables
2) transpose so the hours are now in rows, and all the records are now in columns
3) Create column headers based on: Entity ID, Asset Type and Asset ID
4) each daily table has a date header in a single cell and I need to create a new date column for that daily date (i.e. (01/01/2020) that increments by hour
01/01/2020/ : 01
01/01/2020/ : 02
01/01/2020/ : 24
etc
01/02/2020/ : 01
01/02/2020/ : 02
01/02/2020/ : 024

Thank-you

 

[rkaczano]

I have posted again with a pre and post transpose picture.

I want to:
1) Merge or combine all the separate daily tables
2) transpose so the hours are now in rows, and all the records are now in columns
3) Create column headers based on the combined fields for : Entity ID, Asset Type and Asset ID
4) Create a date column based on the single date reference for each table, and then use this to feed separate newly created Year/Month/Day/Hour columns

Part of the challenge with working with Power Query for the first time is not knowing the vernacular to pose the question properly. I know I am "transposing" data but its not clear if I am "merging" or "combining" the various daily tables. The daily tables will always have 24 columns representing hours but may have a variable amount of records (rows). And the source file(s) are always expressed over a monthly interval (i.e. for January it will be a single excel worksheet that has 31 tables stacked on top of each other - 1 table for each day, with 24 columns (hrs) and variable # rows for records). So I need to combine/merge those 31 tables and then transpose them, delete unwanted rows, create headers, create date column, etc. I will then do the same for additional monthly files which will be stored in a folder.

Thanks again