Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
shamilka
Frequent Visitor

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 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.

 

StatusPNSNBatch no
Installation*ABC43001114
Installation ABC21004113
RemovalABC21003113
Installation ABC11002112
RemovalABC11001112
Removal*ABC32001115

 

Thank you in advance!

2 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @shamilka, different logic:

 

Result

dufoq3_0-1711219153331.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

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"

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @shamilka, different logic:

 

Result

dufoq3_0-1711219153331.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Vijay_A_Verma
Super User
Super User

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"

 

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? 

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"

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

Hi @shamilka, check this:

 

Before

dufoq3_0-1713640442236.png

 

After

dufoq3_1-1713640458391.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors