Unpivoting a "grid", but needing to "add" data first

[dedelman_clng]

I'm not sure this can be done in PowerQuery, but if it can I need some advice

I have a data set that is akin to a calendar grid.  Here is a sample

	3am	7am
Monday
Daily	QC 2286 & TITRATORS	SC1-14003 (solvent concentration)
Daily		SC1-14010(2286)
Daily
Daily
Monthly-first Monday	AE2-25830 (2286)
Monthly-first Monday	AE2-25831 (2286)
Weekly	SC1-14012 (CO2 loading)	SC2-14012 (CO2 loading)
Tuesday
Daily	QC 2186, 2286, TVP & TITRATORS	SC1-14003 (solvent concentration)
Daily		SC1-14010(2286)
Daily
Daily
4x weekly
Weekly	AE2-25707(2286)
Weekly	SC2-25005 (2186, TVP)

The rows that have the name of the day of the week ("Monday", "Tuesday") are blank all the way across.  All of the other rows have at value in at least one column (6 total columns). This same structure repeats for all days of the week.

I can unpivot the grid to make 3am, 7am, etc as attributes, but then I lose the "Monday" and "Tuesday" rows/values because there is no data to unpivot.

I feel like I need another column that fills in "Monday" until it hits "Tuesday" then fills in 'Tuesday", etc. Desired outcome would be like this

		3am	7am
Monday	Monday
Monday	Daily	QC 2286 & TITRATORS	SC1-14003 (solvent concentration)
Monday	Daily		SC1-14010(2286)
Monday	...
Tuesday	Tuesday
Tuesday	Daily	QC 2186, 2286, TVP & TITRATORS	SC1-14003 (solvent concentration)
Tuesday	Daily		SC1-14010(2286)
Tuesday	...

I would then unpivot the "Time" columns and have the data I need.

EXPECTED RESULT

Monday	Daily	3am	QC 2286 & TITRATORS
Monday	Monthly-first Monday	3am	AE2-25830 (2286)
Monday	Monthly-first Monday	3am	AE2-25831 (2286)
Monday	Weekly	3am	SC1-14012 (CO2 loading)
Monday	Daily	7am	SC1-14003 (solvent concentration)
Monday	Daily	7am	SC1-14010(2286)
Monday	Weekly	7am	SC2-14012 (CO2 loading)
Tuesday	Daily	3am	QC 2186, 2286, TVP & TITRATORS
Tuesday	Daily	7am	SC1-14003 (solvent concentration)
Tuesday	Daily	7am	SC1-14010(2286)

Any help on getting that additional column populated (or an alternative method of obtaining the desired outcome) would be appreciated.

Thanks!

David

[dedelman_clng]

Thanks @CNENFRNL  - worked great with one tweak

    cols = Table.ColumnNames(Sheet1_Sheet),

[dedelman_clng]

Hi @CNENFRNL -

Thank you for the idea, but there is not a consistent number of rows for each day (ranges from 6-8). I have attached an Excel showing the raw data alongside the desired result.  The bold/highlighted rows contain the value in the far left that needs to be replicated to each of the rows in its block.

David

[CNENFRNL]

Hi, @dedelman_clng , you might want to try such a solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVXbaoNQEPyV4HPap/6AiTcSDWpMbCJ5EGKJNKRglDZ/3zk36dnWJKUUDuwMeHZ0Z9cVi8KK3k778mKNLRq7cWE5ZX1kORuYAA4QACEQawrEFHCBGRABCRUgPGAOLGgFEb5eHt7aw/Hy8FI353bUO82I1eyaeEXEKy7Oq+qV111K6IWzrjobO5Wnj9G7si8i1QV9c2vifq2nc5LOZXp/Mrj7XG8d8SzxwxZ87WVDmtnc0G+Jfis249A1dwzn77Ox/3c1pMfJ1bRtD6f5ra4rW5kKoiL+AM8TF44jiIm8pjZ/w3xfEXv+TJEuCgJFrMhcERcty7ZrjO3z/mUJQ2IfB5ogiogAB9+/LozjkblbEDEO+LQ6c/9EfadxrGhAkCSKBgRpqug3r2P3CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"3am" = _t, #"7am" = _t, #"11am" = _t, #"3pm" = _t, #"7pm" = _t, #"11pm" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(Source)),

    //"Monday"/"Tuesday"/... row contains only one non-null cell
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if List.NonNullCount(Record.ToList(_))=1 then [#"(blank)"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"(blank)", "Custom"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

[dedelman_clng]

Thank you @CNENFRNL 

Where do I put the "let _t = ..." code if my Source code looks like this

let
    Source = Excel.Workbook(File.Contents("H:\DEdelman\LIMS\Sample Schedule.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

[CNENFRNL · Accepted solution]

@dedelman_clng , you may tweak the code this way,

let
    Source = Excel.Workbook(File.Contents("H:\DEdelman\LIMS\Sample Schedule.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    cols = Table.ColumnNames(Source),
    #"Replaced Value" = Table.ReplaceValue(Sheet1_Sheet,"",null,Replacer.ReplaceValue, cols),

    //"Monday"/"Tuesday"/... row contains only one non-null cell
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if List.NonNullCount(Record.ToList(_))=1 then Record.ToList(_){0} else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down", {cols{0}, "Custom"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

I suggest you turning the range of dataset into Excel table; so that 

[CNENFRNL]

Hi, @dedelman_clng , I'm afraid that I couldn't quite catch from your sample data to the desired result; but it seems such a transformed index column would help keep relevant rows tracable in subsequent steps.

let
    Source = ...

    #"Added Index" = Table.AddIndexColumn(#"previous step", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 6), Int64.Type}})
...