Power Query - EMT1257 - Tylko jedna tabela do przekształcenia

[@excelisfun]

Thanks for the video, O Masterful +Bill Szysz!!!! I wish I could speak Polish... I got the first 7 steps:
1. Import who sheet
2. Duplicate first date column
3. Data, Analyze Date to get errors
4. Replace Errors with Null
5. Fill Dates Down
6. Group By for last column - Group by Table - Result is 10 tables
...

[@excelisfun]

+ExcelIsFun I think I got the last few steps now...
each Table.UnpivotOtherColumns.TablePromotrHeaders(_) , {“Species”}, “Date”, “Value”
)
Filter out column
Date and Number Type
Amazing Bill!!!!!
Thanks for your awesomeness!

[@BillSzysz1]

+ExcelIsFun Sorry for late answer, Mike :-((
You are absolutely right.
Normały we receive from Table.Group only "_" (and this is a table). But we can use "_" as an argument for Table.PromoteHeaders inside Table.Group function. And then we can use Table.PromoteHeaders(_) as a first argument for the second function - Table.Unpivot - everything inside Table.Group function.
This is an example how can we use nested functions in PQ :-)
Thanks for watchng :-)

[@excelisfun]

+Bill Szysz , Thanks for your Masterful Innovation with Power Query and thanks for teaching us! On Friday, I spent 3 hours watching and re-watching this video until I got all the steps (I hope). I then made a video so I can share your great trick with our awesome Online Excel team!!! I will post it tomorrow... I hope I got it right...

[@excelisfun]

+Bill Szysz These were the final steps that I used:

1. Select
A1, Ctrl + Shift + End (31 date columns and 1 Species Column)

2.
Ctrl + T (uncheck Table has Field Names)

3.
Import into Power Query as Table

4.
Select whole table and add the Data Type: Text
(Transform Ribbon Tab, Any Column Group, Data Type = Text)

5.
Duplicate first date column (Right-lick and click
Duplicate Column)

6.
Select Duplicate Date Column: Transform Ribbon
Tab, Data & Time Column Group, Date, Parse. This gives errors for
non-dates

7.
Select Duplicate Date Column: Transform Ribbon
Tab, Any Column Group, Replace Errors = Null

8.
Select Duplicate Date Column: Transform Ribbon
Tab, Any Column Group, Fill Down

9.
Select Duplicate Date Column: Transform Ribbon
Tab, Table Group, Group By: 1) Duplicate Date Column, 2) Name = Table, 3)
Operation = All Rows

10.
Edit code in formula bar: =
Table.Group(#"Filled Down", {"Column2 - Copy"},
{{"Table", each Table.UnpivotOtherColumns(Table.PromoteHeaders(_) ,
{"Species"}, "Date", "Value") , type table}})

11.
Remove First Column

12.
Expand All Button, uncheck “Use original column
name as prefix”

13.
Filter out “Column 33”

14.
Parse Dates

15.
Data Type for Value = Whole Number

16.
Close and Load To

[@excelisfun]

+ExcelIsFun , Last Parse Dates was becasue there were typing errors on at least one of the dates.

[@pmsocho]

5:47 - Wooooow!
9:39 - Wooooow!
11:52 - Wooooow!
12:32 - Woooow!
Już się nie mogę doczekać tego szkolenia! :) Ja też się przy okazji fajnych rzeczy nauczę :)

[@BillSzysz1]

+pmsocho "przy okazji" to ja się nauczę spoooooro od Ciebie ;-)
Fajnie że Ci się podobało :-)

[@davidmoss836]

do you post in English ?anywhere ?

[@BillSzysz1]

+David Moss Thanks for watching :-)
You can download the file with this solution if you want. Link is below the video.
And here is version made by Mike "ExcelIsFun" Girvin (of course in english).
Follow the link (below):
ruclips.net/video/HSqKnEiByTQ/видео.html