Forum Discussion

shamilka's avatar
shamilka
Frequent Visitor
2 years ago
Solved

Copy rows which has certain data types

Hi All,    I have to extract below rows marked in (*) from this table and copy the whole row in another new table where I can use to make a power BI dashboard for further investigation.   As per ...
Need Help
Show and Tell
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi shamilka, different logic:

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrLknMyUksyczP01LSUXJ0cjYBUsYGBoZAytDQRClWB1WVAkSVEUjawMAErMoYrCooNTe/LDEHRd4YSR6LKYYQVWDFhkaYpkDlDTHloY4FmW8EU2CqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, PN = _t, SN = _t, #"Batch no" = _t]),
    GroupedRows = Table.Group(Source, {"Batch no"}, {{"All", each _, type table}, {"RowCount", each Table.RowCount(_), Int64.Type}}),
    FilteredRows = Table.SelectRows(GroupedRows, each ([RowCount] = 1)),
    Combined = Table.Combine(FilteredRows[All])
in
    Combined
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago
    let
Source = PostgreSQL.Database("A", "B"),
J_history = Source{[Schema="X",Item="Y"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(J_history,{"PN", "SN", "Batchno", "AJ", "vm", "created_date"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([vm] = "YJ" or [vm] = "YK")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [created_date] > 19077),
#"Grouped Rows" = Table.Combine(Table.Group(#"Filtered Rows1", {"PN"}, {{"All", each Table.SelectRows(_, (x)=> if List.ContainsAll([Status], {"Removal", "Installation"}) then false else List.Contains({"Removal", "Installation"}, x[Status]))}})[All])
in
    #"Grouped Rows"
shamilka's avatar
shamilka
Frequent Visitor
2 years ago

Hi Vijay_A_Verma , 

 

Thanks a lot for your input. My source code in the advanced filter goes like this 

 

let
Source = PostgreSQL.Database("A", "B"),
J_history = Source{[Schema="X",Item="Y"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(J_history,{"PN", "SN", "Batchno", "AJ", "vm", "created_date"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([vm] = "YJ" or [vm] = "YK")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [created_date] > 19077)
in
#"Filtered Rows1"

 

Could you please let me know how can I include this code for the exhisting code above? 

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago
let
Source = PostgreSQL.Database("A", "B"),
J_history = Source{[Schema="X",Item="Y"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(J_history,{"PN", "SN", "Batchno", "AJ", "vm", "created_date"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([vm] = "YJ" or [vm] = "YK")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [created_date] > 19077),
#"Grouped Rows" = Table.Combine(Table.Group(#"Filtered Rows1", {"PN"}, {{"All", each Table.SelectRows(_, (x)=> if List.ContainsAll([Status], {"Removal", "Installation"}) then false else List.Contains({"Removal", "Installation"}, x[Status]))}})[All])
in
    #"Grouped Rows"
  • shamilka's avatar
    shamilka
    Frequent Visitor
    2 years ago

    Hi Vijay, 

     

    I have to come back to you to add another logic to the same code. 

     

    I need to filter more data in the following criteria. For the parts which has only removal or installation is available (completed by the first code), I have to exclude the rows which has a different batch no and same PN as well as the same qty.  

     

    Can you please modify the code of this?

     

    StatusPNSNBatch noQTYPRemarks 
    InstallationABC550011141XXEDifferent batch number, same qty, same PNexclude
    RemovalABC550021151XXEDifferent batch number, same qty, same PNexclude
    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Hi shamilka, check this:

       

      Before

       

      After

       

      Query with sample data:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrLknMyUksyczPU9JRcnRyNgFSxgYGhkDK0BBMKsXqoCpUgKg0AkkaGJiAVRoDSSOwyqDU3PyyxBwUNcZwNaa4TDOEqARrMDSCq0QxDarGEJ8asENgagywuh+i0BSkH6bQBK4QxTCoGoijTCFqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, PN = _t, SN = _t, #"Batch no" = _t, QTY = _t]),
    GroupedRows = Table.Group(Source, {"Batch no"}, {{"All", each _, type table}, {"RowCount", each Table.RowCount(_), Int64.Type}}),
    FilteredRows = Table.SelectRows(GroupedRows, each ([RowCount] = 1)),
    Combined = Table.Combine(FilteredRows[All]),
    fn_1 = (tbl as table)=>
        let 
            SortInner = Table.Sort(tbl, {{"QTY", Order.Ascending}}),
            GroupedRowsInner = Table.Group(SortInner, {"QTY"}, {{"All", each _, type table}, {"RowCount", each Table.RowCount(_), type table}},
                GroupKind.Local,
                (s,c)=> Byte.From( s[QTY] <> c[QTY] )),
            FilteredInner = Table.SelectRows(GroupedRowsInner, each [RowCount] = 1),
            CombinedInner = Table.Combine(FilteredInner[All])
        in 
            CombinedInner,
    GroupedRows2 = Table.Group(Combined, {"PN"}, {{"All", each _, type table}, {"fn_1", each fn_1(_), type table}}),
    Combined2 = Table.Combine(GroupedRows2[fn_1])
in
    Combined2

       

       

      Query applied to your steps:

       

      let
    Source = PostgreSQL.Database("A", "B"),
    J_history = Source{[Schema="X",Item="Y"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(J_history,{"PN", "SN", "Batchno", "AJ", "vm", "created_date", "QTY"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([vm] = "YJ" or [vm] = "YK")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [created_date] > 19077),
    GroupedRows = Table.Group(#"Filtered Rows1", {"Batchno"}, {{"All", each _, type table}, {"RowCount", each Table.RowCount(_), Int64.Type}}),
    FilteredRows = Table.SelectRows(GroupedRows, each ([RowCount] = 1)),
    Combined = Table.Combine(FilteredRows[All]),
    fn_1 = (tbl as table)=>
        let 
            SortInner = Table.Sort(tbl, {{"QTY", Order.Ascending}}),
            GroupedRowsInner = Table.Group(SortInner, {"QTY"}, {{"All", each _, type table}, {"RowCount", each Table.RowCount(_), type table}},
                GroupKind.Local,
                (s,c)=> Byte.From( s[QTY] <> c[QTY] )),
            FilteredInner = Table.SelectRows(GroupedRowsInner, each [RowCount] = 1),
            CombinedInner = Table.Combine(FilteredInner[All])
        in 
            CombinedInner,
    GroupedRows2 = Table.Group(Combined, {"PN"}, {{"All", each _, type table}, {"fn_1", each fn_1(_), type table}}),
    Combined2 = Table.Combine(GroupedRows2[fn_1])
in
    Combined2