PQ: Formula Sort Only Not Blank/Rows Data

[muhammad susanto]

hi all..

how to sort data exclude blank/empty rows based on column C "name of city" .
data empty in column C till BU, the other column are contains formulas.
how to make formula in PQ to fix this problem. my target in sheet "buku register"
i have 5 excel files to combine them with criteria above.
here attachment file & capture before & after
anyone help, thank in advance
.sst

note:
i have use filter but after when refreshing data the filter is gone

[sandy666]

there is no an example excel files before / after

[muhammad susanto]

hi sandy...
here new attachment file before & after sort
i want to when refreshing data, blank cell/empty cell keep don't show

[sandy666]

Add all workbooks one by one to the new excel file
Append BUKU REGISTER tables
Select columns from A to BU then Unpivot Other Columns
Filter by null (name of city)
Select Value then Attribute column then Pivot Column
___
Conditions:
all headers should be the same in all workbooks

and again you've a big mess in your files, unnecessary tables which doesn't work
Your data contain improper type of data, you've mixed numbers and text so this is a wrong idea
First you need clean and "repair" your source data
I cannot do real example with five files because you attached only one source file.
So here is an example M-code with single source, test it by yourself

let
    Source = Excel.Workbook(File.Contents("C:\remove_blank\before_sort.xlsx"), null, true),
    #"BUKU REGISTER_Sheet" = Source{[Item="BUKU REGISTER",Kind="Sheet"]}[Data],
    Promote = Table.PromoteHeaders(#"BUKU REGISTER_Sheet", [PromoteAllScalars=true]),
    UOC = Table.UnpivotOtherColumns(Promote, {"REF BPHTB", "REF Verif", "Name of city", "NOMOR REGISTER LELANG", "TANGGAL REGISTER LELANG", "NO. TIKET PERMOHONAN ONLINE", "NAMA PEMOHON LELANG/PENJUAL", "NAMA DAN JABATAN YANG MENANDATANGANI SURAT PERMOHONAN LELANG", "KATEGORI  PEMOHON LELANG/PENJUAL", "NOMOR SURAT PERMOHONAN LELANG", "TANGGAL SURAT PERMOHONAN LELANG", "NAMA DEBITUR", "BEA PERMOHONAN LELANG", "TANGGAL SETOR BEA PERMOHONAN KE KAS NEGARA", "NOMOR NTPN BEA PERMOHONAN", "NOMOR HASIL PENELITIAN KELENGKAPAN BERKAS", "TANGGAL HASIL PENELITIAN KELENGKAPAN BERKAS", "NOMOR SURAT KELENGKAPAN BERKAS", "TANGGAL SURAT KELENGKAPAN BERKAS LELANG", "NOMOR SURAT JAWABAN KELENGKAPAN BERKAS", "TANGGAL SURAT JAWABAN KELENGKAPAN BERKAS", "NOMOR SURAT PENETAPAN JADWAL LELANG", "TANGGAL SURAT PENETAPAN JADWAL LELANG", "TEMPAT PELAKSANAAN LELANG", "NOMOR RISALAH LELANG", "NOMOR URUT LOT BARANG", "KODE LOT LELANG", "TANGGAL RISALAH LELANG", "STATUS LELANG", "NAMA PEJABAT LELANG/PELELANG", "NIP PEJABAT LELANG", "NOMOR DAN TANGGAL SK PENGANGKATAN PEJABAT LELANG", "NOMOR DAN TANGGAL ST PEJABAT LELANG", "NAMA PEJABAT PENJUAL", "NOMOR DAN TANGGAL SURAT PENUNJUKAN PEJABAT PENJUAL", "JENIS LELANG", "SUB JENIS LELANG", "JENIS PENAWARAN", "JENIS TRANSAKSI", "URAIAN BARANG", "KATEGORI BARANG", "UMKM", "NOP/NJOP", "SIFAT BARANG", "JUMLAH PESERTA LELANG", "JAMINAN PENAWARAN LELANG", "NILAI LIMIT", "NOMOR REGISTER PEMBATALAN LELANG", "ALASAN PEMBATALAN LELANG", "SURAT PERMINTAAN BEA LELANG BATAL", "NAMA PEMENANG LELANG/PEMBELI", "ALAMAT PEMBELI", "NOMOR IDENTITAS PEMBELI", "NPWP PEMENANG LELANG/PEMBELI", "TANGGAL PEMBAYARAN OLEH PEMBELI", "POKOK LELANG", "HASIL BERSIH LELANG", "TANGGAL PENYETORAN HASIL BERSIH LELANG", "BEA LELANG PENJUAL", "BEA LELANG PEMBELI", "BEA LELANG BATAL", "PPH", "PPN", "PENERIMAAN NEGARA LAINNYA", "TANGGAL PENYETORAN KE KAS NEGARA", "NOMOR NTPN BEA LELANG", "BPHTB", "TANGGAL SURAT SETOR BPHTB", "TANGGAL PENYELESAIAN MINUTA RL", "NOMOR DAN TANGGAL BERITA ACARA PEMBETULAN MINUTA/RL", "TANGGAL PENYERAHAN KUTIPAN RL", "NOMOR PERKARA GUGATAN KE PENGADILAN", "KETERANGAN"}, "Attribute", "Value"),
    Filter = Table.SelectRows(UOC, each ([Name of city] = "KPKNL Padang")),
    PC = Table.Pivot(Filter, List.Distinct(Filter[Attribute]), "Attribute", "Value")
in
    PC

btw. PQ doesn't support excel formulas or Data Validation list

[muhammad susanto]

hi sandy, thank but not work
i confuse in this line Select Value then Attribute column then Pivot Column , after i do that show message "Invalid cell value '#N/A'
i try to copy this formula but not work show mesage "Data.format.Error;Invalid cell value '#N/A

let
    Source = Excel.Workbook(File.Contents("C:\remove_blank\before_sort.xlsx"), null, true),
    #"BUKU REGISTER_Sheet" = Source{[Item="BUKU REGISTER",Kind="Sheet"]}[Data],
    Promote = Table.PromoteHeaders(#"BUKU REGISTER_Sheet", [PromoteAllScalars=true]),
    UOC = Table.UnpivotOtherColumns(Promote, {"REF BPHTB", "REF Verif", "Name of city", "NOMOR REGISTER LELANG", "TANGGAL REGISTER LELANG", "NO. TIKET PERMOHONAN ONLINE", "NAMA PEMOHON LELANG/PENJUAL", "NAMA DAN JABATAN YANG MENANDATANGANI SURAT PERMOHONAN LELANG", "KATEGORI  PEMOHON LELANG/PENJUAL", "NOMOR SURAT PERMOHONAN LELANG", "TANGGAL SURAT PERMOHONAN LELANG", "NAMA DEBITUR", "BEA PERMOHONAN LELANG", "TANGGAL SETOR BEA PERMOHONAN KE KAS NEGARA", "NOMOR NTPN BEA PERMOHONAN", "NOMOR HASIL PENELITIAN KELENGKAPAN BERKAS", "TANGGAL HASIL PENELITIAN KELENGKAPAN BERKAS", "NOMOR SURAT KELENGKAPAN BERKAS", "TANGGAL SURAT KELENGKAPAN BERKAS LELANG", "NOMOR SURAT JAWABAN KELENGKAPAN BERKAS", "TANGGAL SURAT JAWABAN KELENGKAPAN BERKAS", "NOMOR SURAT PENETAPAN JADWAL LELANG", "TANGGAL SURAT PENETAPAN JADWAL LELANG", "TEMPAT PELAKSANAAN LELANG", "NOMOR RISALAH LELANG", "NOMOR URUT LOT BARANG", "KODE LOT LELANG", "TANGGAL RISALAH LELANG", "STATUS LELANG", "NAMA PEJABAT LELANG/PELELANG", "NIP PEJABAT LELANG", "NOMOR DAN TANGGAL SK PENGANGKATAN PEJABAT LELANG", "NOMOR DAN TANGGAL ST PEJABAT LELANG", "NAMA PEJABAT PENJUAL", "NOMOR DAN TANGGAL SURAT PENUNJUKAN PEJABAT PENJUAL", "JENIS LELANG", "SUB JENIS LELANG", "JENIS PENAWARAN", "JENIS TRANSAKSI", "URAIAN BARANG", "KATEGORI BARANG", "UMKM", "NOP/NJOP", "SIFAT BARANG", "JUMLAH PESERTA LELANG", "JAMINAN PENAWARAN LELANG", "NILAI LIMIT", "NOMOR REGISTER PEMBATALAN LELANG", "ALASAN PEMBATALAN LELANG", "SURAT PERMINTAAN BEA LELANG BATAL", "NAMA PEMENANG LELANG/PEMBELI", "ALAMAT PEMBELI", "NOMOR IDENTITAS PEMBELI", "NPWP PEMENANG LELANG/PEMBELI", "TANGGAL PEMBAYARAN OLEH PEMBELI", "POKOK LELANG", "HASIL BERSIH LELANG", "TANGGAL PENYETORAN HASIL BERSIH LELANG", "BEA LELANG PENJUAL", "BEA LELANG PEMBELI", "BEA LELANG BATAL", "PPH", "PPN", "PENERIMAAN NEGARA LAINNYA", "TANGGAL PENYETORAN KE KAS NEGARA", "NOMOR NTPN BEA LELANG", "BPHTB", "TANGGAL SURAT SETOR BPHTB", "TANGGAL PENYELESAIAN MINUTA RL", "NOMOR DAN TANGGAL BERITA ACARA PEMBETULAN MINUTA/RL", "TANGGAL PENYERAHAN KUTIPAN RL", "NOMOR PERKARA GUGATAN KE PENGADILAN", "KETERANGAN"}, "Attribute", "Value"),
    Filter = Table.SelectRows(UOC, each ([Name of city] = "KPKNL Padang")),
    PC = Table.Pivot(Filter, List.Distinct(Filter[Attribute]), "Attribute", "Value")
in
    PC

[sandy666]

You didn't read my post precisely, I think.
Clean and Repair your data in source file, clean Name Manager, no mixed type of data, no manually entered #N/A, no "-" entered by formatting - and try again
btw. my M-code it was as an example not copy/paste solution

[muhammad susanto]

hi sandy, thank you, i will try.