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

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"

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    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'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"
  • dufoq3's avatar
    dufoq3
    Community Champion
    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