Hi Bill, I've seen your brilliant comments in some Power Query forums, so I looked up your youtube channel. I'm watching and taking notes hoping to learn from you. I hope it is ok that I add my notes to this page. You can correct any misunderstandings and perhaps it will help others. Thanks! -Kathy Kardynalski Disclaimer: despite my name I speak no Polish. I followed along with my Excel in English to make sure I was clicking the same buttons. :) This video shows how to organize "dirty" data. Steps: From worksheet, select cells of raw data and bring into Power Query using “From Table". In Power Query, Promote first rows as headers. Change headers for first two columns to reflect column contents. Data columns are formatted as "any", select all data columns, format as text (Note: this is necessary to perform next step. Otherwise next step will result in errors because “Expression.Error: we cannot convert value 10 to type Text. Details: Value=10 Type=Type” - who thinks these error messages are informative???) Back to the query: there are several pairs of Product/Quantity columns, merge each with a delimiter (:) Select Column 2 (“Sklep”= store?), filter out null to remove empty top row. Select all data columns and Unpivot Columns. This step puts all the data into two Columns: Attribute and Value. The Attribute column contains what was the column headers, which are not needed. Delete this column. The Value column contains the merged data separated by colons. Split this column by delimiter (colon). Even though the pivoted columns were formatted as text, when you split the column, Power Query detects the type and changes the type of the product table to text and the type of the quantity table (with numbers) as whole numbers. [Note: In the demo the non-numeric fields in the quantity column show as errors; in my tests they show as null. I am not sure why there is a difference here or if that difference is important.) Select product row and filter out blanks. You have your cleaned up data. Close and load to table. On worksheet, select a cell in the table and Insert Pivot Table Add fields (I think here Bill realized he should have renamed columns in the query rather than Value.1 and Value.2) :) Store and Value.1 in rows, Value.2 in Values Pivot table looks good. What happens when you add more data? Go to Sheet2 copy additional data and paste at the bottom of the table of Raw Data Go back to result table and refresh data. New data appears. (There is a problem with one result. Looking at data, discover it is in the wrong column. Adjust manually.) Refresh again. Must refresh query result table before refreshing pivot table.
Zaczynam zgłębiać nauki o Power query. Pomalutku małymi kroczkami. Dzięki że pokazujesz jak to działa. Pozdrawiam. :)) Mam jeszcze jedno pytanie: w pracy mam pakiet office 2010 bez Accesa i nie wiem czy do tej wersji excela którą mam w pracy można instalować power query czy też muszą być spełnione jakiś dodatkowe warunki co do wersji pakietu office?
+aknoimak Dzięki za zainteresowanie :-) Power Query to "magia w czystej postaci" ale taka którą można łatwo okiełznać :-) Im głębiej w nią wejdziesz tym większym "czarodziejem" bedziesz :-) Czy jest dostepny dla Twojej wersji Office przekonasz sie instalując dodatek u siebie (wymagania były zmieniane przez Microsoft więc to jest najprostszy sposób przekonania się czy wersja Office jest odpowiednia). Jeśli Office jest 32bitowy to i instalujemy 32bitową wersję PQ a jeśli Office 64bit to PQ też 64bit. Pozdrawiam
Thanks, Excel Master Szysz!!!
Thanks, Excel Guru Mike :-)))
Thank you problem with price list and horizontally range solved when I want upload data in vertically way
Hi Bill, I've seen your brilliant comments in some Power Query forums, so I looked up your youtube channel. I'm watching and taking notes hoping to learn from you. I hope it is ok that I add my notes to this page. You can correct any misunderstandings and perhaps it will help others. Thanks!
-Kathy Kardynalski
Disclaimer: despite my name I speak no Polish. I followed along with my Excel in English to make sure I was clicking the same buttons. :)
This video shows how to organize "dirty" data. Steps:
From worksheet, select cells of raw data and bring into Power Query using “From Table".
In Power Query, Promote first rows as headers.
Change headers for first two columns to reflect column contents.
Data columns are formatted as "any", select all data columns, format as text
(Note: this is necessary to perform next step. Otherwise next step will result in errors because “Expression.Error: we cannot convert value 10 to type Text. Details: Value=10 Type=Type” - who thinks these error messages are informative???)
Back to the query: there are several pairs of Product/Quantity columns, merge each with a delimiter (:)
Select Column 2 (“Sklep”= store?), filter out null to remove empty top row.
Select all data columns and Unpivot Columns.
This step puts all the data into two Columns: Attribute and Value. The Attribute column contains what was the column headers, which are not needed. Delete this column.
The Value column contains the merged data separated by colons. Split this column by delimiter (colon).
Even though the pivoted columns were formatted as text, when you split the column, Power Query detects the type and changes the type of the product table to text and the type of the quantity table (with numbers) as whole numbers. [Note: In the demo the non-numeric fields in the quantity column show as errors; in my tests they show as null. I am not sure why there is a difference here or if that difference is important.)
Select product row and filter out blanks. You have your cleaned up data.
Close and load to table.
On worksheet, select a cell in the table and Insert Pivot Table
Add fields (I think here Bill realized he should have renamed columns in the query rather than Value.1 and Value.2) :)
Store and Value.1 in rows, Value.2 in Values
Pivot table looks good.
What happens when you add more data? Go to Sheet2 copy additional data and paste at the bottom of the table of Raw Data
Go back to result table and refresh data. New data appears.
(There is a problem with one result. Looking at data, discover it is in the wrong column. Adjust manually.) Refresh again.
Must refresh query result table before refreshing pivot table.
Zaczynam zgłębiać nauki o Power query. Pomalutku małymi kroczkami. Dzięki że pokazujesz jak to działa.
Pozdrawiam. :))
Mam jeszcze jedno pytanie: w pracy mam pakiet office 2010 bez Accesa i nie wiem czy do tej wersji excela którą mam w pracy można instalować power query czy też muszą być spełnione jakiś dodatkowe warunki co do wersji pakietu office?
+aknoimak
Dzięki za zainteresowanie :-) Power Query to "magia w czystej postaci" ale taka którą można łatwo okiełznać :-)
Im głębiej w nią wejdziesz tym większym "czarodziejem" bedziesz :-)
Czy jest dostepny dla Twojej wersji Office przekonasz sie instalując dodatek u siebie (wymagania były zmieniane przez Microsoft więc to jest najprostszy sposób przekonania się czy wersja Office jest odpowiednia).
Jeśli Office jest 32bitowy to i instalujemy 32bitową wersję PQ a jeśli Office 64bit to PQ też 64bit.
Pozdrawiam