Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I have data somewhat like this as shown below
and now I want to extract the data into the one below
Fundamentally I want to get the first Test which is not cleared and denoted as N. I have tried but not able to make that workout, any help will be appreciated.
Solved! Go to Solution.
Hi @Anonymous ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRcgZiQ6VYHaiQEZDrB8RGCCFjqJAxQsgEKmSCEDKFmmUKFgoIgJiro6SAUILDOmdM65wxrUPTaIoiBLHOiCjr/FCFjFHcjeI7Y0zrjJGsM8a0zg+fdRBdJhBdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, #"C/N" = _t, Quality = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}, {"C/N", type text}, {"Quality", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Quality] = null then [#"C/N"] else null),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"all", each _, type table [Desc=nullable text, #"C/N"=nullable text, Quality=nullable number, Custom=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.SelectRows([all], each [#"C/N"]="N")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each Table.First([Custom.1])),
#"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.2", {"Desc", "Quality"}, {"Desc", "Quality"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.2",{{"Desc", "First Test Not Cleared"}, {"Custom", "C/N"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"all", "Custom.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"Pass",Replacer.ReplaceValue,{"First Test Not Cleared"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "Desc", each "PPC"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Desc", "C/N", "First Test Not Cleared", "Quality"})
in
#"Reordered Columns"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @Anonymous ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlEwVNJRcgZiQ6VYHaiQEZDrB8RGCCFjqJAxQsgEKmSCEDKFmmUKFgoIgJiro6SAUILDOmdM65wxrUPTaIoiBLHOiCjr/FCFjFHcjeI7Y0zrjJGsM8a0zg+fdRBdJhBdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, #"C/N" = _t, Quality = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}, {"C/N", type text}, {"Quality", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Quality] = null then [#"C/N"] else null),
#"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"all", each _, type table [Desc=nullable text, #"C/N"=nullable text, Quality=nullable number, Custom=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.SelectRows([all], each [#"C/N"]="N")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each Table.First([Custom.1])),
#"Expanded Custom.2" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.2", {"Desc", "Quality"}, {"Desc", "Quality"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.2",{{"Desc", "First Test Not Cleared"}, {"Custom", "C/N"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"all", "Custom.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"Pass",Replacer.ReplaceValue,{"First Test Not Cleared"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "Desc", each "PPC"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Desc", "C/N", "First Test Not Cleared", "Quality"})
in
#"Reordered Columns"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum