Fungsi transformasi dalam Power Query untuk manipulasi data

APLIKASI TO: Azure Data Factory Azure Synapse Analytics

Tip

Data Factory di Microsoft Fabric adalah generasi Azure Data Factory berikutnya, dengan arsitektur yang lebih sederhana, AI bawaan, dan fitur baru. Jika Anda baru menggunakan integrasi data, mulailah dengan Fabric Data Factory. Beban kerja ADF yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik real time, dan pelaporan.

• Mulai uji coba gratis Fabric.
• Upgrade dari Azure Data Factory ke Data Factory di Microsoft Fabric.

Manipulasi Data dalam Azure Data Factory memungkinkan Anda melakukan persiapan dan perselisihan data gesit bebas kode dalam skala cloud dengan menerjemahkan skrip Power Query M ke dalam skrip Data Flow. ADF terintegrasi dengan Power Query Online dan membuat fungsi Power Query M tersedia untuk perselisihan data melalui eksekusi Spark menggunakan infrastruktur Spark aliran data.

Saat ini tidak semua fungsi Power Query M didukung untuk manipulasi data meskipun tersedia selama penulisan. Saat membuat mash-up, Anda akan diminta dengan pesan kesalahan berikut jika fungsi tidak didukung:

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

Di bawah ini adalah daftar fungsi Power Query M yang didukung.

Pengelolaan Kolom

• Pilihan: Table.SelectColumns
• Penghapusan: Table.RemoveColumns
• Penggantian nama: Table.RenameColumns, Table.PrefixColumns, Table.TransformColumnNames
• Pengurutan ulang: Table.ReorderColumns

Pemfilteran Baris

Gunakan fungsi M Table.SelectRows untuk memfilter dengan kondisi berikut:

• Kesetaraan dan ketidaksetaraan
• Perbandingan numerik, teks, dan tanggal (tetapi bukan TanggalWaktu)
• Informasi numerik seperti Number.IsEven/Odd
• Penahanan teks menggunakan Text.Contains, Text.StartsWith, atau Text.EndsWith
• Rentang tanggal termasuk semua Fungsi tanggal 'IsIn')
• Kombinasi penggunaan kondisi and, or, atau not

Menambahkan dan Mentransformasi Kolom

Fungsi M berikut menambahkan atau mentransformasi kolom: Table.AddColumn, Table.TransformColumns, Table.ReplaceValue, Table.DuplicateColumn. Berikut adalah fungsi transformasi yang didukung.

• Aritmetika numerik
• Perangkaian teks
• Aritmetika Tanggal dan Waktu (Operator aritmetika, Date.AddDays, Date.AddMonths, Date.AddQuarters, Date.AddWeeks, Date.AddYears)
• Durasi dapat digunakan untuk aritmetika tanggal dan waktu, tapi harus ditransformasi menjadi jenis lain sebelum ditulis ke sink (Operator aritmetika, #duration, Duration.Days, Duration.Hours, Duration.Minutes, Duration.Seconds, Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, Duration.TotalSeconds)
• Sebagian besar fungsi numerik standar, saintifik, dan trigonometri (Semua fungsi dengan Operasi, Pembulatan, dan Trigonometrikecuali Number.Factorial, Number.Permutations, dan Number.Combinations)
• Penggantian (Replacer.ReplaceText, Replacer.ReplaceValue, Text.Replace, Text.Remove)
• Ekstraksi teks posisional (Text.PositionOf, Text.Length, Text.Start, Text.End, Text.Middle, Text.ReplaceRange, Text.RemoveRange)
• Pemformatan teks dasar (Text.Lower, Text.Upper, Text.Trim/Start/End, Text.PadStart/End, Text.Reverse)
• Fungsi Tanggal/Waktu (Date.Day, Date.Month, Date.YearTime.Hour, Time.Minute, Time.Second, Date.DayOfWeek, Date.DayOfYear, Date.DaysInMonth)
• Jika ekspresi (tetapi cabang harus memiliki jenis yang cocok)
• Filter baris sebagai kolom logika
• Konstanta angka, teks, logika, tanggal, dan tanggalwaktu

Tabel Penggabungan

• Power Query akan menghasilkan gabungan berlapis (Table.NestedJoin; pengguna juga dapat menulis Table.AddJoinColumn). Kemudian, pengguna harus memperluas kolom gabungan bersarang ke dalam gabungan yang tidak bersarang (Table.ExpandTableColumn, tidak didukung dalam konteks lain).
• Fungsi M Table.Join dapat ditulis secara langsung untuk menghindari perlunya langkah perluasan tambahan, tetapi pengguna harus memastikan bahwa tidak ada nama kolom duplikat di antara tabel gabungan
• Jenis Penggabungan yang didukung: Inner, LeftOuter, RightOuter, FullOuter
• Kedua Value.Equals dan Value.NullableEquals didukung sebagai pembanding kesetaraan kunci

Kelompokkan berdasarkan

Gunakan Table.Group untuk menggabungkan nilai.

• Harus digunakan dengan fungsi agregasi
• Fungsi agregasi yang didukung: List.Sum, List.Count, List.Average, List.Min, List.Max, List.StandardDeviation, List.First, List.Last

Pengurutan

Gunakan Table.Sort untuk mengurutkan nilai.

Mengurangi Baris

Pertahankan dan Hapus Bagian Atas, Pertahankan Rentang (fungsi M yang sesuai, hanya mendukung jumlah, bukan kondisi: Table.FirstN, Table.Skip, Table.RemoveFirstN, Table.Range, Table.MinN, Table.MaxN)

Fungsi yang tidak didukung yang diketahui

Fungsi	Status
Table.PromoteHeaders	Tidak didukung. Hasil yang sama dapat dicapai dengan mengatur "Baris pertama sebagai header" di himpunan data.
Table.CombineColumns	Ini adalah skenario umum yang tidak didukung secara langsung, tetapi dapat dicapai dengan menambahkan kolom baru yang menggabungkan dua kolom tertentu. Misalnya, Table.AddColumn(RemoveEmailColumn, "Nama", tiap [NamaDepan] & " " & [NamaBelakang])
Table.TransformColumnTypes	Ini didukung dalam banyak kasus. Skenario berikut tidak didukung: mengubah string ke jenis mata uang, mengubah string ke jenis waktu, mengubah string menjadi jenis Persentase dan mengubah dengan lokal.
Table.NestedJoin	Hanya melakukan gabungan akan mengakibatkan kesalahan validasi. Kolom harus diperluas agar berfungsi.
Table.RemoveLastN	Hapus baris bawah tidak didukung.
Table.RowCount	Tidak didukung, tetapi dapat diperoleh dengan menambahkan kolom kustom yang berisi nilai 1, lalu menggabungkan kolom tersebut dengan List.Sum. Table.Group didukung.
Penanganan kesalahan tingkat baris	Penanganan kesalahan tingkat baris saat ini tidak didukung. Misalnya, untuk memfilter nilai non-numerik dari kolom, salah satu pendekatan adalah mengubah kolom teks menjadi angka. Setiap sel yang gagal ditransformasi akan berada dalam status kesalahan dan perlu difilter. Skenario ini tidak dimungkinkan dalam M yang diskalakan.
Table.Transpose	Tidak didukung

Solusi skrip M

SplitColumn

Alternatif untuk pemisahan menurut panjang dan menurut posisi dicantumkan di bawah

• Table.AddColumn(Sumber, "Karakter pertama", tiap Text.Start([Email], 7), teks jenis)
• Table.AddColumn(#"Karakter pertama disisipkan", "Rentang teks", tiap Text.Middle([Email], 4, 9), teks jenis)

Opsi ini dapat diakses dari opsi Ekstrak di pita

Table.CombineColumns

• Table.AddColumn(RemoveEmailColumn, "Name", tiap [FirstName] & " " & [LastName])

Pivot

• Pilih transformasi Pivot dari editor PQ dan pilih kolom pivot Anda

• Selanjutnya, pilih kolom nilai dan fungsi agregat

• Ketika Anda mengklik OK, Anda akan melihat data di editor diperbarui dengan nilai pivot
• Anda juga akan melihat pesan peringatan bahwa transformasi mungkin tidak didukung
• Untuk memperbaiki peringatan ini, perluas daftar dengan pivot secara manual menggunakan editor PQ
• Pilih opsi Editor Lanjutan dari pita
• Memperluas daftar nilai dengan pivot secara manual
• Mengganti List.Distinct() dengan daftar nilai seperti ini:

#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

Memformat kolom tanggal/waktu

Untuk mengatur format tanggal/waktu saat menggunakan Power Query ADF, ikuti set ini untuk mengatur format.

1. Pilih kolom di UI Power Query dan pilih Ubah Jenis > Tanggal/Waktu
2. Anda akan melihat pesan peringatan
3. Buka Editor Lanjutan dan ubah TransformColumnTypes menjadi TransformColumns. Tentukan format dan budaya berdasarkan data input.

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

Konten terkait

Pelajari cara buat Power Query manipulasi data di ADF.