Forum Discussion
shamilka
2 years agoFrequent 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 ...
- 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 - 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
Community Champion
2 years agoHi 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