Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
Hi, there is no grouping necessary.
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.
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.
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |