Urgent help needed

[bn333]

Hi,

I need help with this, I weekly get spreadsheet looks likes below. I need to extract dates and employees present on that date using power query, please note sheets are saved in sharepoint folder. Final output to look like second snippet

Final output:

[Anonymous · Accepted solution]

Hi @bn333 ,
Thanks for SamWiseOwl reply.
Sample data

Column1	Column2	Column3	Column4	Column5	Column6
	4/1/2024	4/2/2024	4/3/2024	4/4/2024	4/5/2024
DAILY TOTAL	6	7	7	7	7
	Bank Holiday	Jazzy D	Glassy	Suzy	Momo
		Faty	Kam	Lolo	Glassy
	4/8/2024	4/9/2024	4/10/2024	4/11/2024	4/12/2024
DAILY TOTAL	21	27	25	44	24
	Leslie	Lolo	Gogo	Teliut	Koko
	Fifi	Momo	Koko	Nigek	Nuytu
	Bibi	Nuytu	Puytyu	Koko	Fifi
	4/15/2024	4/16/2024	4/17/2024	4/18/2024	4/19/2024
DAILY TOTAL	1	2	3	4	5
	A	B	C	D	E

You can try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVHLDoIwEPwV0jMJtoKPI74f+EjkYgiHGtE0VHuAHuDr3UWg9WDCdIftLJ0pSUKIS3yPemzA/IYyQ4eG+oYGX5q6CVmE2+jqxKc4jGBrBBj/ADVAZvydOxslxZ1X8LrjdV05C2BryYsCWxddYzmol+qG4FnxErt7/oI1UlKZkVbkexNjbGooHVjcCkfZP/OM4oK2WYBS1LdCPDwrpMgsF+qJJc6k0CVaVHlvfCUeosvS7rjkKJ5ZjlVXpe7vRdxE33PJGWqlzUzzoT4oDawYI4uPLW7dBp3+i9okBQxRBwi6Q0L0BJgD8PcsSZp+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    FilterDailyTotal = Table.SelectRows(AddIndex, each [Column1] = "DAILY TOTAL"),
    AddCustom = Table.AddColumn(AddIndex, "Custom", each if [Column1] = "DAILY TOTAL" then Text.From(List.PositionOf(FilterDailyTotal[Index], [Index])+1) else null),
    AddPreviousCustom = Table.AddColumn(AddCustom, "PreviousCustom", each if [Column1] = "DAILY TOTAL" then null else try AddCustom{[Index]}[Custom] otherwise null),
    FillCustom = Table.FillDown(AddPreviousCustom,{"PreviousCustom"}),
    RemoveIndex = Table.RemoveColumns(FillCustom,{"Index", "Custom", "Column1"}),
    GroupedTables = Table.Group(RemoveIndex, {"PreviousCustom"}, {{"AllData", each _}}),
    TransformTables = Table.TransformColumns(GroupedTables, {"AllData", each Table.Transpose(Table.RemoveColumns(_, {"PreviousCustom"}))}),
    #"Expanded AllData" = Table.ExpandTableColumn(TransformTables, "AllData", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllData",{"PreviousCustom", "Column2"}),
    UnpivotedColumns = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1"}, "Attribute", "Value"),
    FilteredRows = Table.SelectRows(UnpivotedColumns, each ([Value] <> null)),
    RenamedColumns = Table.RenameColumns(FilteredRows,{{"Column1", "Date"}, {"Value", "Name"}}),
    RemovedColumns = Table.RemoveColumns(RenamedColumns,{"Attribute"})
in
    RemovedColumns

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SamWiseOwl]

That is fantastic! I didn't even think to try it that way.

I had got to the point of identifying which rows are dates and which arent.

Then I'd pivoted to have TRUE and FALSE as column headers.

Couldn't work out the last push.

Very nice!

[bn333]

You are Mcode Genius, Guru everything ,THANK YOU SO MUCH , it worked.

[SamWiseOwl]

Hi @bn333 

You can use the Unpivot option to turn the date row into a column.

If you create a new blank query and paste this code in it will give you an idea.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjDUNzDRNzIwMgFxjJA5xsgcE2SOKZwTqxOt5JKYmVOpEJJfkpgDlDQDYnMUDFIDZDgl5mUreOTnZKYkVgK5XolVVZUKLkCWe05icTFIKLi0CkT55ufmwzQBkVtiCUjUOycxF0j55OfkI/TExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {}, "Attribute", "Value")
in
#"Unpivoted Columns"

[bn333]

Hi Sam,

I followed the steps but in that I am only getting names for the 1st week 1-5, not getting any names for the second week 8-12. please help Thanks

[SamWiseOwl]

How much variation is there?
Can there be more than 2 weeks?

How many rows of people can there be?

The more variations the harder it gets.

[bn333]

I receive sheets every week, so variation per week and number of employees will vary it is not constant, if not power query is there any other way I can achieve the same result?

[bn333]

looks like nobody is able to help