Copy rows which has certain data types

[shamilka]

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 the example below the Batch number is the key here. If there is a removal and installation available form the same batch number, we can ignore it. If the batch number is available only for removal or only installation, the whole row has to be copied (as per the example, Batch number 114 & 115 has to be copied) for another table to be used to make a dashboards or even get a daily report to my email.

It would be great if I can get the power query code with a daily auto-refresh and extract data for the last 3 days from the database.

Status	PN	SN	Batch no
Installation*	ABC4	3001	114
Installation	ABC2	1004	113
Removal	ABC2	1003	113
Installation	ABC1	1002	112
Removal	ABC1	1001	112
Removal*	ABC3	2001	115

Thank you in advance!

[dufoq3 · Accepted solution]

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

[dufoq3 · Accepted solution]

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]

You can use this code by pasting it in Advanced Editor.
Replace Source line with your Source line after testing.
Your Source line will be something like 
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrLknMyUksyczPU9JRcnRyNgFSxgYGhkDK0NBEKVYHmyIjkKyBgQlYkTFYUVBqbn5ZYg6KvDGSPKYhhhBFYLWGRpiGQOUNccmDTDeCyZsqxcYCAA==", 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]),
    #"Grouped Rows" = Table.Combine(Table.Group(Source, {"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]

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 · Accepted solution]

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]

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?

Status	PN	SN	Batch no	QTY	P	Remarks
Installation	ABC5	5001	114	1	XXE	Different batch number, same qty, same PN	exclude
Removal	ABC5	5002	115	1	XXE	Different batch number, same qty, same PN	exclude

[dufoq3]

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