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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.