Results 1 to 7 of 7

Thread: PQ: Formula Sort Only Not Blank/Rows Data

  1. #1
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0

    PQ: Formula Sort Only Not Blank/Rows Data

    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
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by muhammad susanto; 02-18-2022 at 01:40 PM.

  2. #2
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7
    there is no an example excel files before / after
    Last edited by sandy666; 02-19-2022 at 05:04 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  3. #3
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    hi sandy...
    here new attachment file before & after sort
    i want to when refreshing data, blank cell/empty cell keep don't show
    Attached Files Attached Files

  4. #4
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool

    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
    Code:
    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
    Last edited by sandy666; 02-20-2022 at 03:39 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  5. #5
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    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
    Code:
    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
    Last edited by muhammad susanto; 02-21-2022 at 08:30 AM.

  6. #6
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7
    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
    Last edited by sandy666; 02-21-2022 at 11:29 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  7. #7
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    hi sandy, thank you, i will try.

Similar Threads

  1. Sort Data In Excel Using Formula
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 07-08-2014, 10:01 AM
  2. Replies: 2
    Last Post: 03-08-2014, 02:49 AM
  3. Replies: 18
    Last Post: 02-12-2014, 10:47 AM
  4. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  5. Replies: 5
    Last Post: 07-11-2013, 07:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •