Urgent help needed

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:

Need Help

Anonymous

1 year ago

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

Urgent help needed.pbix37 KB

8 Replies

Anonymous

Not applicable

1 year ago

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

Urgent help needed.pbix37 KB

SamWiseOwl
Super User
1 year ago
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
New Member
1 year ago
You are Mcode Genius, Guru everything ,THANK YOU SO MUCH , it worked.

SamWiseOwl
Super User
1 year ago
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
  New Member
  1 year ago
  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
    Super User
    1 year ago
    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.

Forum Discussion

Urgent help needed

8 Replies

Related content

Need Urgent Help on SamePeriodLastYear

urgent help needed

Need Urgent help

urgent help needed

urgent help needed