Forum Discussion
Stuck in PQ and not sure how to achieve next steps
Jpalaci1 Check out this M code (paste in the advanced editor), you'll be able to retrace my steps.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Account Name", type text}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}, {"Total", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Total"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Column1.2", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Column1.2.2"}),
#"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column1.2.1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1.1] <> null)),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Column1.1", "Column1.1 - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Column1.1", "Column1.2.1", "Column1.1 - Copy", "Account Name", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Account Name] = null then [Column1.1] else null),
#"Filled Down1" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each [Custom] <> "TOTALS (USD)"),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows1",{"Custom", "Column1.1", "Column1.2.1", "Column1.1 - Copy", "Account Name", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"Column1.1"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns2", each ([Account Name] <> null))
in
#"Filtered Rows2"
Thank you so much! this really helps!
Two questions:
1) How do I stack each column onto each other/transpose the list? For example:
Employee Amount Month
Employee 1 x 1
Employee 2 x 1
Employee 3 x 1
Employee 1 x 2
Employee 2 x 2
Employee 3 x 2
Employee 1 x 3
Employee 2 x 3
Employee 3 x 3
2) Are there any resources/M Code resources where there already made/examples of Excel funtions? For example M Code equivalent of:
=IFS
=IFS
=UNIQUE(FILTER(
=LEFT(SEARCH(
=XLOOKUP(
=A1=B1 (to return Boolean T or F for data checks)
=WEEKNUM
=IF(AND(
Thank you again so much!
JKPieterse could correct me, my variant
1) Table Transform - remove Employee and Account # columns, select Name, unpivot other columns, Group By Name and Month. Please see in attached.
2) I strongly recommend not to map Excel formulas into Power Query, that's ineffective approach. Think in terms of records and lists (aka rows and columns), not in terms of cells and their values. Merging, grouping, pivoting/unpivoting are usually used. As for date/text/numbers transformation check Transform tab from left to right, majority of the are here.
3) It's always better to attach the file, scripts within posts are more for other people who will check for similar solutions.
Last question. When I get my data it comes as a whole new data set. I don't just append the latest months as the new month's file can have corrections for the previous months. Should I put into a folder the raw data and when I get the latest take out last month's and replace the newest in the folder? Should I just do a new query using by pasting the M Code every month a new file comes in?
Thank you so much for the help!
If all these files have the same structure, you may collect them in some folder and use From Folder connector to transform all files at once. To keep only latest figures, on final step sort result by name and month and remove duplicates.
Ideal situation if you build this reporting system and forget about it for years. For example, Flow drops attachments from emails into the folder upon receiving; power query in background performs scheduled refreshes and returns result to publish it on web or in another Excel.
Another question:
If I have questions about my M Code it's best to copy what I got so far in Advanced Editor and share that?
Thank you!
