Forum Discussion
Solved
Convert compact pivot like table to tabular form using power query
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 t...
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 CombinedAllHi, there is no grouping necessary.
- Fill down Level-2, Level-3 and Level-4
- 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
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
Hi, there is no grouping necessary.
- Fill down Level-2, Level-3 and Level-4
- 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
Thank you.
You're welcome.