Formatowanie danych w usłudze LuckyTemplates: adresowanie nieregularnych formatów danych

W dzisiejszym poście na blogu omówimy problem tygodnia nr 6 . Pokażę Ci jak formatować dane w LuckyTemplates z wykorzystaniem Power Query . Poniższy zrzut ekranu przedstawia rozwiązanie, które powinieneś mieć po wykonaniu wszystkich transformacji danych. Rzeczywisty numer salda wynosił 685 616,33 USD . Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

W wyzwaniu nr 6 wzięliśmy udział bardzo licznie. Doceniam wysiłki wszystkich osób starających się to osiągnąć. Należy zauważyć, że widzieliśmy kilka wpisów, które miały niewielką różnicę. Pokażę ci, przeglądając moje zgłoszenie do tego wyzwania, dlaczego ta kwota w dolarach była zaniżona.

Zauważ, że moje rozwiązanie dotyczące formatowania danych w obejmowało wiele kroków i że było wiele wpisów, które streszczały te kroki. Wezmę te skróty pod uwagę w przyszłych przedsięwzięciach, które mam z tego typu danymi. Tak więc uczestnicy nie tylko pomogli rozwiązać Problem Tygodnia, ale także pomogli mi na dłuższą metę, jeśli chodzi o tworzenie raportów.

Otworzę zapytanie o moc i omówię krok po kroku, w jaki sposób wymyśliłem moje rozwiązanie. Oto jak wygląda gotowy produkt.

Ale jeśli kliknę źródło, zobaczysz, że to właśnie pojawiło się, gdy połączyliśmy się z lokalizacją pliku, w którym przechowywaliśmy plik CSV.

Spis treści

• Korzyści z komentarzy
• Przyczyny niewłaściwej kwoty
• Kolumny filtrujące
• Tworzenie kolumny Wiek
• Wniosek

Korzyści z komentarzy

Sprawdź kroki po prawej stronie. Możesz zobaczyć małe „i”, które jest skrótem od informacji. Oznacza to, że w edytorze zaawansowanym zostały wprowadzone komentarze.

Otwórzmy tutaj bardzo szybko zaawansowany edytor. Widać, że teksty zaznaczone na zielono to komentarze, które pomagają mi przypomnieć sobie, o czym wtedy myślałem.

Pozostawia ślad po tym, co zrobiłem w przeszłości, więc jeśli ktoś nowy wejdzie do akt, może przynajmniej mieć pojęcie o ścieżce, którą podążałem.

Tutaj możemy zobaczyć komentarze z boku. Pierwszy komentarz do Changed Type1 dotyczy zasadniczo zmiany typów dla różnych kolumn. Jak widać, mój komentarz brzmiał: „Upewnij się, że bieżąca kolumna jest ustawiona jako ułamek dziesiętny, bo w przeciwnym razie sprawy zaczną się psuć!”

To prowadzi mnie do punktu, dlaczego ludzie wymyślają tę niewłaściwą kwotę salda, którą pokazałem ci wcześniej.

Przyczyny niewłaściwej kwoty

Pozwól, że pokażę ci inny plik PBIX, który skopiowałem od kogoś, kto miał jeden z tych wpisów. 685 615,28 $ to niewłaściwa kwota, a było wiele wpisów, które miały tę wynikową kwotę.

Zastanówmy się, jak i dlaczego tak się stało. Jeśli przejdziemy do zakładki Transform i klikniemy Detect Data Type , która automatycznie wykryje wszystkie wiersze, zobaczymy, że bieżąca kolumna pojawia się jako liczba całkowita 64, co nie jest tym, co chcemy zobaczyć.

Dlatego kwoty są błędne. Istnieje również inny sposób, w jaki ten błąd może pojawić się w Twoich ustawieniach. Przejdźmy do Plik>Opcje i ustawienia>Opcje.

W obszarze GLOBALNYM i sekcji Ładowanie danych możemy zobaczyć Wykrywanie typów . Wybrałem opcję Wykryj typy kolumn i nagłówki dla źródeł nieustrukturyzowanych zgodnie z ustawieniami każdego pliku .

Jeśli wybierzesz pierwszą opcję, czyli Zawsze wykrywane typy kolumn i nagłówki dla źródeł nieustrukturyzowanych , również wystąpiłby błąd w kwocie salda.

Jednym z naszych , który bardzo nam pomógł, jeśli chodzi o kod M lub język zaawansowanych zapytań, jest Melissa. Pokazała mi dokument firmy Microsoft , który wyjaśni nam, dlaczego wystąpił ten błąd.

Jak widać, kiedyś nazywało się to Opcje projektu . Opcja Automatycznie wykrywaj typy kolumn i nagłówki dla źródeł nieustrukturyzowanych została zaznaczona.

Dokument mówi również, że opiera się tylko na pierwszych 200 wierszach twojej tabeli.

W mojej tabeli pierwotna liczba wierszy wynosiła około 4000 wierszy. Dlatego nie zebrał odpowiednich kwot.

Wróćmy do Power Query i zacznijmy formatować dane w LuckyTemplates. Jak widać w kroku Changed Type , jest to liczba całkowita 64 z prądem.

Więc to, co zamierzam tutaj zrobić naprawdę szybko, to przefiltrować według jednego z tych numerów faktur, aby pokazać, jak to wygląda przed i po.

Po wybraniu konkretnego numeru faktury widzimy, że kwota prądu wynosi 4741,01.

Gdy kliknę ten typ zmiany, zobaczysz, że wartość się zmieniła i została zaokrąglona do 4741.

Jest to więc szybki i łatwy sposób wyjaśnienia, co poszło nie tak z innymi wpisami do Problemu Tygodnia.

Wróćmy do mojego rozwiązania w zapytaniu potęgowym M. Następnym krokiem jest odfiltrowanie wierszy, aby wyświetlić tylko puste wiersze.

Następnym krokiem jest miejsce, w którym usunąłem kolumny. Usunąłem wszystkie kolumny z wyjątkiem tych, które widzisz na zrzutach ekranu. Należy również zauważyć, że numer rekordu, który pierwotnie się pojawił, jest w rzeczywistości numerem faktury.

Następnym krokiem jest usunięcie pustych wierszy, które były puste dla każdego pola.

Następnie zmieniłem nazwę pierwszej kolumny, która wcześniej była pusta i zmieniłem ją na Klient… ponieważ zamierzamy dodać kolejne pole, które będzie moim rzeczywistym polem Klient.

Następnym krokiem było zastąpienie wszystkich wartości pustego pola na null w kolumnie Klient. Powodem, dla którego musimy to zrobić, jest następny krok.

Aby wypełnić wszystkie te wiersze, żaden z nich nie może być pusty i musi być pusty.

Aby to zrobić, kliknij na kolumnę, przejdź do Fill , następnie Down .

W ten sposób możesz zobaczyć wszystkie nazwiska w tej kolumnie.

Kolumny filtrujące

Przefiltrowałem tutaj również kilka wierszy i przefiltrowałem według numeru faktury, aby usunąć te, które były puste.

Dodałem kolejną kolumnę Klient, w której skopiowałem kolumnę z powyższego zrzutu ekranu i pozbyłem się liczb przed nazwą.

Zmieniłem też kolejność kolumn. Przeniosłem tę nową kolumnę klientów od samego końca do samego początku. Następnie usunąłem kolumnę Klient, która miała na końcu kropkę lub kropkę. Więc teraz mam tylko jedną wyraźną kolumnę Klient bez liczb po lewej stronie.

Następnym krokiem jest Inserted Sum, gdzie wziąłem wszystkie wartości z Current, 1 – 30, 31 – 60, 61 – 90 i 91 – 120. Dodałem wszystkie te kolumny razem, aby uzyskać niestandardową kolumnę o nazwie Addition .

Innym sposobem na to jest zaznaczenie wszystkich kolumn, przejście do zakładki Dodaj kolumnę , kliknięcie Standard, a następnie kliknięcie Dodaj .

Dałoby to pole Dodatek bez konieczności wpisywania czegokolwiek w zapytaniu o moc.

A potem usunąłem wszystkie oryginalne kolumny: Current, 1 – 30, 31 – 60, 61 – 90 i 91 – 120. Następnie zmieniłem nazwę na Balance .

Następnym krokiem jest dodanie niestandardowej kolumny. W przypadku tego wyzwania 21 stycznia pobrano te oryginalne dane od klienta.

Data automatycznie pojawiła się jako funkcja tekstowa, więc w tym kroku zmieniłem ją na funkcję daty.

Tworzenie kolumny Wiek

Aby utworzyć kolumnę wieku, zaznaczyłem dzisiejszą datę i termin porodu. Następnie poszedłem do zakładki Dodaj kolumnę i kliknąłem Odejmij dni .

Następnym krokiem jest zamiana tego na liczbę całkowitą.

Następnie zmieniłem nazwę kolumny z Age na Days Aged . Następnie dodałem jeszcze dwie kolumny warunkowe. Jednym z nich jest zmiana kategorii tych elementów w kolumnie starzenia.

Druga kolumna warunkowa pomogłaby posortować starzejącą się kolumnę, aby były wyświetlane w kolejności sekwencyjnej.

Wniosek

W ten sposób wymyśliłem rozwiązanie tego problemu tygodnia. Kiedy kliknę Zamknij i zastosuję, oto wyniki.

Mam nadzieję, że podobało Ci się omawianie rozwiązania ze mną. Jeśli podobał Ci się ten samouczek dotyczący formatowania danych w usłudze LuckyTemplates, zasubskrybuj kanał telewizyjny , aby uzyskać dostęp do podobnych treści.

Jarretta