Convert compact pivot like table to tabular form using power query

[Devkota_Samrat]

I got a complex data in a format like (compact pivot table),
I need to convert it into tabular form,
I tried fill down in power query to clean this data.

I have made a small example to explain this problem.
first picture is the small simulation of the problem,
in second picture after using fill down the red part is unwanted,
and yellow highlighted part is also not required in my final data.

beforeafter

[dufoq3 · Accepted solution]

Hi @Devkota_Samrat,,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVDbCsIwDP2V0Of9xOwQB4IPCjLGHlobXNjIoM1A/95NKMKg6BQhEM7JObnVtcpDQFGZmqPJXnhLN3TwRGFZm6JkMXwl22PULATv8GmFXY/eI0t6m6MMl271CmtxgVYG//cx2oQWcoGN4Q4MOygZdlP+qu+ejKWe5J7g4msjRxgSVu3R0Xw/FCPCmaQlhgMjVGj8R7N+5poH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level-1" = _t, #"Level-2" = _t, #"Level-3" = _t]),
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"Level-1"}, 
        {{"All", each Table.SelectRows(Table.FillDown(_, Table.ColumnNames(ReplaceBlankToNull)), (x)=> x[#"Level-3"] <> null) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[Devkota_Samrat]

Thank you @dufoq3 ,
I have attached the google drive link of my sample file,
link ,
this file has three sheets,
input sheet is the source data I want to clean and final output is what I want,
Output one is the intermediate step in which red colored rows are deleted to get final result if I have to do this task manually in excel.

Regards,
Samrat

[dufoq3 · Accepted solution]

Hi, there is no grouping necessary. 

1. Fill down Level-2, Level-3 and Level-4
2. remove null rows for Level-5

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVTBbtswDP0VIWcf0h1279ymMNBiRp21h64HRmYSIrLkSXTb/P3obE7aRGoCZAN4sfX0+MhH8elpdBkC8igb7eI52/2d0BvWavMV4giJwjLYBc0MfkTuwSRyZzWGQM6GrARGyyG7JY3932zqocYG/CpkyDpOIPH3ngJbq3u3BsOEIQ4dqOOnl1p3TWeErlZX2HrUBCzS1D58emJ1t72kn914/OWr+taRqckuQrKO/LEoD1L9ieHymbrfqYoclQYsD3LvQC/Jol+n276B91w77NnyZLy48+K9mhBz365MTZ0zYdB1/aujthG/k7q+z+fi8jtgFDXpvKU+1UA85Et4Ay0xGApSQ+Xm/Aoe/4kZ/xH+gEvSBtMDd+fYebWFnZEw77yXVh/bCxU7vUo/l3t4lVli9AQ7w3PXhK6B2aeFAFn14XbCRVkzA1ccQC9ktiyp5zNzHqRz62MzVvGmVSDIgrFJ5CxBr2Aho3dMfQVNKzvnE6rCvogY52X/ndKOYqrEXlm/mxuJpNpDGzl6dH6lCqtK7xbyXtPeHAAPURU2JO/PUljKlN04V6fpIlh1czE+Al6GLbj8cTpx+ZBHd9RJSve5EnljKZ9/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level-1" = _t, #"Level-2" = _t, #"Level-3" = _t, #"Level-4" = _t, #"Level-5" = _t]),
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    FilledDown = Table.FillDown(ReplaceBlankToNull,{"Level-2", "Level-3", "Level-4"}),
    FilteredRows = Table.SelectRows(FilledDown, each ([#"Level-5"] <> null))
in
    FilteredRows

[Devkota_Samrat]

Thank you.

[dufoq3]

You're welcome.

[dufoq3]

Hi again @Devkota_Samrat, I've noticed one mistake in query above:

In query below it has been repaired:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVDbCsIwDP2V0Of9xOwQB4IPCjLGHlobXNjIoM1A/95NKMKg6BQhEM7JObnVtcpDQFGZmqPJXnhLN3TwRGFZm6JkMXwl22PULATv8GmFXY/eI0t6m6MMl271CmtxgVYG//cx2oQWcoGN4Q4MOygZdlP+qu+ejKWe5J7g4msjRxgSVu3R0Xw/FCPCmaQlhgMjVGj8R7N+5poH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level-1" = _t, #"Level-2" = _t, #"Level-3" = _t]),
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    FilledDown = Table.FillDown(ReplaceBlankToNull,{"Level-2"}),
    GroupedRows = Table.Group(FilledDown, {"Level-1", "Level-2"},
        {{"All", each Table.SelectRows(Table.FillDown(_, Table.ColumnNames(FilledDown)), (x)=> x[#"Level-3"] <> null) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[dufoq3 · Accepted solution]

Hi @Devkota_Samrat,,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVDbCsIwDP2V0Of9xOwQB4IPCjLGHlobXNjIoM1A/95NKMKg6BQhEM7JObnVtcpDQFGZmqPJXnhLN3TwRGFZm6JkMXwl22PULATv8GmFXY/eI0t6m6MMl271CmtxgVYG//cx2oQWcoGN4Q4MOygZdlP+qu+ejKWe5J7g4msjRxgSVu3R0Xw/FCPCmaQlhgMjVGj8R7N+5poH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level-1" = _t, #"Level-2" = _t, #"Level-3" = _t]),
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    GroupedRows = Table.Group(ReplaceBlankToNull, {"Level-1"}, 
        {{"All", each Table.SelectRows(Table.FillDown(_, Table.ColumnNames(ReplaceBlankToNull)), (x)=> x[#"Level-3"] <> null) , type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll