Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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
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 @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
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?
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 |
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
58 | |
42 | |
28 | |
22 |