CONCATENATEX trong Power Query. Chức năng TEXTJOIN mới thật tuyệt vời. Bạn có thể làm điều tương tự với Power Query không? Đúng. Bây giờ bạn có thể.
Xem video
- Người xem tải xuống dữ liệu từ một hệ thống mà mỗi mục được phân tách bằng Alt + Enter
- Bill: Tại sao bạn lại làm điều này? Người xem: Đó là cách tôi kế thừa dữ liệu. Tôi muốn giữ nó theo cách đó.
- Bill: Bạn muốn làm gì với 40% giá trị không có trong bảng? Người xem: Không có câu trả lời
- Bill: Có một cách phức tạp để giải quyết vấn đề này nếu bạn có các công cụ Power Query mới nhất.
- Thay vào đó, một Macro VBA để giải quyết nó - macro sẽ hoạt động trở lại Excel 2007
- Thay vì thực hiện hàm VLOOKUP, hãy thực hiện một loạt các Tìm & Thay thế bằng VBA
Bản ghi video
Học Excel từ, Podcast Tập 2151.
Tôi thực sự không biết phải gọi cái này là gì. Nếu tôi đang cố gắng thu hút những người sử dụng DAX, tôi sẽ nói ConcatenateX trong Power Query, hoặc chỉ những người sử dụng Excel thông thường nhưng sử dụng Office 365, tôi sẽ nói TEXTJOIN trong Power Query, hoặc hoàn toàn trung thực, đó là một tập hợp các bước siêu phức tạp trong Power Query để kích hoạt một giải pháp siêu điên rồ trong Excel.
Chào. Chào mừng bạn trở lại netcast. Tôi là Bill Jelen. Hôm qua trong tập 2150, tôi đã mô tả vấn đề. Một người nào đó đã gửi tệp này trong đó hệ thống của họ đang tải xuống các mục là một đơn đặt hàng có nguồn cấp dữ liệu giữa chúng. Nói cách khác, ALT + ENTER, và thấy rằng, WRAP TEXT được bật và họ muốn thực hiện một VLOOKUP thành LOOKUPTABLE này cho từng mục đó. Tôi như thế nào? Tại sao anh làm điều này? Nhưng tôi đã che điều đó ngày hôm qua. Hãy thử và tìm cách làm điều này.
Tôi thực sự đã nói rằng, Power Query sẽ là cách tốt nhất để làm điều này nhưng tôi vẫn chưa biết cách thực hiện phần cuối cùng. Tôi nói, có ổn không nếu mỗi mặt hàng kết thúc ở hàng riêng của nó? Không, họ phải quay lại trình tự ban đầu này. Tôi thích, điều đó thật kinh khủng, nhưng, trên nguồn cấp dữ liệu Twitter của tôi mới tuần trước, Tim Rodman, ngày 27 tháng 9: “Cuối cùng cũng đọc cuốn sách này,” - Tôi đoán đó là PowerPivot Alchemy - “và đã có được điều ước ConcatenateX của anh ấy. ” Tôi đã là một người thông minh khi tôi làm điều này, yêu cầu PERHAPS ROMANX, nhưng có lẽ tôi thực sự muốn ConcatenateX, và vì vậy Tim đã cho tôi biết rằng bây giờ tôi có thể làm điều đó trong Power BI.
Vì vậy, tôi đã đến gặp những người bạn của mình, Rob Collie tại Power Pivot Pro và Miguel Escobar, và bạn biết đấy, họ đều là tác giả của những cuốn sách hay. Tôi có cả hai cuốn sách này, nhưng tính năng này quá mới, không có trong cuốn sách nào cả. Tôi nói, này, các bạn có biết làm thế nào để làm điều này? Và Miguel giành được giải thưởng vì Miguel đã dậy sớm vào sáng nay hoặc đêm qua - tôi không chắc là giải nào - và đã gửi mã.
Được rồi, đây là kế hoạch trong Power Query và kế hoạch này rất phức tạp. Tôi không bao giờ viết ra một kế hoạch trong Power Query. Tôi chỉ đi làm tất cả mọi thứ. Tôi sẽ bắt đầu với dữ liệu ban đầu, thêm một cột INDEX để chúng ta có thể giữ các mục trong một đơn hàng cùng nhau, SPLIT COLUMN thành ROWS bằng cách sử dụng LINEFEED. Đây là lần thứ hai hoặc thứ ba trên podcast tôi sử dụng tính năng mới này. Thật tuyệt làm sao. Tôi có một cột INDEX thứ hai để chúng tôi có thể sắp xếp các mục vào trình tự ban đầu và sau đó LƯU NHƯ MỘT KẾT NỐI.
Sau đó, chúng ta sẽ đến với bảng LOOKUP, đặt nó thành một bảng, truy vấn từ bảng, LƯU NHƯ KẾT NỐI - đó sẽ là phần dễ dàng nhất ngay tại đó - và sau đó hợp nhất truy vấn này và truy vấn này dựa trên mục số, tất cả các mục từ bảng bên trái, đây là bảng bên trái, khớp từ bên phải, thay thế nulls bằng số mục. Chúng tôi vẫn cập nhật những gì chúng tôi muốn làm khi không tìm thấy điều gì đó vì một lý do nào đó. Tôi đã hỏi câu hỏi này, nhưng người gửi tệp không trả lời, vì vậy tôi sẽ thay nó bằng số mục. Hy vọng rằng điều đúng đắn cần làm là thêm nhiều mục hơn vào LOOKUPTABLE để không có bất kỳ mục nào không phải là founds, nhưng chúng ta đang ở đây, và sau đó chúng ta sẽ sắp xếp theo INDEX1 và INDEX2, theo cách đó,mọi thứ trở lại theo đúng trình tự và sau đó đây là phần mà tôi không thể tìm ra cách làm.
Chúng ta sẽ nhóm theo INDEX1 làm tương đương với TEXTJOIN hoặc ConcatenateX với ký tự 10 là dấu phân cách, là bộ tổng hợp, và tất nhiên, đây là phần khó nhưng là phần thực sự mới ở đây tập hợp các bước này. Vì vậy, nếu bạn hiểu những gì TEXTJOIN làm hoặc có thể khái niệm hóa những gì ConcatenateX sẽ làm, về cơ bản chúng tôi đang làm điều đó bằng cách sử dụng loại bước này. Vậy, được rồi. Vì vậy, chúng ta hãy thử xem.
Vì vậy, chúng ta sẽ bắt đầu ở đây. Đây là dữ liệu ban đầu của chúng tôi, có một tiêu đề. Vì vậy, tôi sẽ ĐỊNH DẠNG LÀ BẢNG, KIỂM SOÁT + T, BẢNG CỦA TÔI CÓ ĐẦU, vâng, và sau đó chúng ta sẽ sử dụng Power Query. Bây giờ, tôi đang ở trong Excel 2016 Office 365, vì vậy nó ở đây ở phần bên trái của tab DỮ LIỆU. Nếu bạn chỉ đang sử dụng Excel 2016, không phải Office 365, thì nó đang ở giữa - NHẬN & CHUYỂN ĐỔI. Nếu bạn đang sử dụng Excel 2010 hoặc 2013, nó sẽ là tab riêng của nó ở đây được gọi là Power Query và nếu bạn không có tab đó, bạn sẽ phải tải xuống tab đó. Nếu bạn đang sử dụng Mac hoặc Android hoặc bất kỳ phiên bản Excel giả mạo nào khác, xin lỗi, không có Power Query nào dành cho bạn. Tải phiên bản Excel dành cho Windows và dùng thử.
Được rồi, chúng ta sẽ thực hiện một Power Query TỪ BẢNG, được rồi, và điều đầu tiên tôi sẽ làm là tôi sẽ THÊM MỘT CỘT CHỈ SỐ và tôi sẽ bắt đầu TỪ 1. Được rồi , vì vậy, về cơ bản đây là đơn hàng 1, đơn hàng 2, đơn hàng 3, đơn hàng 4. Sau đó, chúng tôi sẽ chọn cột này và, trên tab CHUYỂN ĐỔI, chúng tôi sẽ chuyển sang SPLIT COLUMN, BY DELIMITER và họ có thể phát hiện rằng đó là một LineFeed là dấu phân cách. Tôi thích Power Query đang phát hiện điều này. Bây giờ, tại sao Excel, văn bản thành cột, vâng, văn bản thành cột không tìm ra dấu phân cách là gì? Và mỗi lần xuất hiện, chúng ta sẽ TÍCH LŨY VÀO ROWS, và SỬ DỤNG KÝ TỰ ĐẶC BIỆT. Được rồi, vậy là tốt rồi.
Now watch what happens here. We have 999 rows but now we have far more than that. So, every item in that order number is now its own row. Now, the person who asked this question doesn't want it to be its own row but we're going to have to make it be its own row so we can do the join. I'm going to add a new INDEX column here. ADD COLUMN, INDEX COLUMN, FROM 1, and so we have… these are essentially the order numbers and then these are the sequence within the order because I've determined that, later, these are going to be in some other order. I don't know what order they switch to but here we are.
Alright, so, HOME, not the CLOSE & LOAD button but the CLOSE & LOAD drop-down, and CLOSE & LOAD TO. I don't know why it takes 10 seconds for them to display this dialog box the first time. We're going to ONLY CREATE CONNECTION. Click OK. Beautiful. So that's TABLE1, TABLE1.
Now, we're going to go to our LOOKUPTABLE. LOOKUPTABLE is going to be easy to process. We're going to format this as a table. CONTROL+T. Click OK. DATA, or POWER QUERY if you’re in an old version, FROM TABLE. This is going to be called TABLE2. Let's call it LOOKUPTABLE. Perfect. CLOSE & LOAD, CLOSE & LOAD TO, ONLY CREATE CONNECTION.
Alright. Now, we have our two bits over here and I want to merge those two. So, we're just going to go to a new spot and then DATA, GET DATA, COMBINE QUERIES, we're going to do a MERGE, and the table on the left is going to be TABLE1 -- that's our original data -- and we're going to use this ITEM number and we're going to marry that up to the LOOKUPTABLE and that ITEM number. It’s really non-intuitive there you have to click on the ITEMS in both cases to define what the key is, and an OUTER join, ALL FROM FIRST, MATCHING FROM SECOND, and, see, there's 40% of these that are missing from the LOOKUPTABLE. This is all fake data but the original data had 40% missing from the LOOKUPTABLE as well. Really kind of frustrating. Alright. So, here's our ITEM number, our 2 INDEX fields, and then our LOOKUPTABLE here. I'm going to EXPAND that and ask for the DESCRIPTION. Alright, you see we have a bunch of nulls here.
Alright, so, we're going to do a conditional column. Conditional column’s going to say look at this column. If it's = to null, then bring this value over, otherwise, use the value that's in that column. So here, under ADD COLUMN, we’ll do CONDITIONAL COLUMN -- nice little UI that'll walk us through this -- if the LOOKUPTABLEDESCRIPTION EQUALS NULL, then we want to use a COLUMN here of ITEMS, otherwise, we want to use the COLUMN called LOOKUPDESCRIPTION, alright. Click OK, and there we are. There's our CUSTOM column with either the new value from the LOOKUPTABLE or the original value if it's not found. At this point, we can right click and say that we want to REMOVE this column. It was a temporary column, it was a helper column. Now that we have what we need, we don't need that column anymore, and actually, at this point, I don't need this column anymore either. So, I can right click and REMOVE that column. Alright. Now, we have our data here. I want to sort it by the original INDEX. So, SORT ASCENDING. That gets our data into the right sequence, and now that it's sorted, I can actually right click and REMOVE that column.
Alright. Now we’re at the point where, for every item, each order number -- so, this is order number 1, let's say -- I want to have these 4 items separated by a LineFeed character. Now, what I was hoping to be able to do was to come here to TRASNFORM. Instead, if we wanted to GROUP BY and that there'd be some magic here in the GROUP BY, I would say I'm going to concatenate or textjoin all those things, but it doesn't work, alright?
So, here's the set of steps that are new to me that allow this to happen. First thing we want to do is we're going to create a brand new column. That column is just going to be called a TABLECOLUMN and we're going to take ALL ROWS and click OK. Okay. So, when we look inside at this table, we see that we have 2 columns -- one called INDEX and one called CUSTOM -- and we have to remember that name there, alright, and this table unfortunately does not work with STRUCTURED COLUMN. See, EXTRACT VALUES is grayed out. So, this doesn't work with a table. It has to work with a list. I have to convert this table to a list, and this is the part I couldn't figure out and the part that Miguel filled in for me.
So, I'm going to create a CUSTOM COLUMN here and I'm going to call it a LISTCOLUMN and we're going to use a function called TABLE.COLUMN and the table is the thing called TABLECOLUMN, and then which column in there is the thing called CUSTOM. Click OK. Alright, and now these are, instead of a table, it is a list. We're home-free now. TRANSFORM, STRUCTURED COLUMN. I'm going to EXTRACT VALUES. I'm going to create a CUSTOM delimiter USING SPECIAL CHARACTERS, INSERT SPECIAL CHARACTER, LINE FEED, and click OK, and it gives me what I'm looking for. So here’s my original order number. The TABLE, we don’t need anymore, right click and REMOVE that, and we now have our original data using the LOOKUPTABLE where we need it, alright? So, I can right-click and REMOVE this, alright, and then finally, HOME, just straight CLOSE & LOAD, which brings it back into a table in Excel. (=Table.Column((TableColumn),“Custom”))
Alright, but it doesn't look like it worked, does it? That's because, by default, this table does not have WRAP TEXT turned on. So, HOME, WRAP TEXT, and we now have our new data doing the equivalent of a VLOOKUP for each item in the list, and when an item is not found, the original item number is still there, so someone can go piece that back together.
Now, the beautiful thing with Power Query is that while it took us some time to get this set up the first time, the next time we download this list, we just copy it here, and we can even edit something. So, let’s change one. So, MANGO, 4954, we’ll take that 7036 and change it to 4954. Alright, so, now the underlying data has changed, all we have to do is come here to this and click the REFRESH which will refresh all of these items, and we get here to SHEET11 and that second item has changed to a MANGO, alright? It’ll take you some time to set this up once but, once you get it set up, it's just a simple matter of refreshing the data and Power Query will go through all those steps.
Bây giờ, đây là điểm tôi thường rủ bạn đi mua sách của tôi nhưng hôm nay, thay vì rủ bạn đi mua sách của Miguel. Miguel Escobar và Ken Puls đã viết cuốn sách xuất sắc này trên M Is For (DATA) MONKEY - cuốn sách hay nhất trên Power Query. Đi kiểm tra đó ra.
Được rồi, tóm lại: hôm nay là một tập thực sự dài; chúng tôi có một trình xem, tải xuống dữ liệu từ một hệ thống mà mỗi mục được phân tách bằng ALT + ENTER và chúng tôi đang cố gắng thực hiện một VLOOKUP cho từng mục riêng lẻ; đã xây dựng một giải pháp ngày nay bằng cách sử dụng Power Query bao gồm công cụ cột có cấu trúc của trích xuất như; nhưng điều đó chỉ hoạt động trên một danh sách, không phải một bảng, vì vậy tôi đã phải sử dụng hàm TABLE.COLUMN để chuyển đổi bảng thành một danh sách.
Chà, này. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2151.xlsm