Stuck in PQ and not sure how to achieve next steps

Question

Hi, I need some help with a Power Query query. I’ve got stuck in the middle somewhere and have been Googling and searching and hit a roadblock. I know the exact output I want but not sure how to get there via PQ.

Steps performed so far:

Take the range and create a table (the format of how the data spits it out seems to warrant this to get correct range of data)
From Table/Range
Check data types automatically changed by PQ and all right
Column 1.1 Transform>Split Column by delimiter>split by comma
Select Total (last column)>Home>Remove Column
Select column 1.2>Split Column by delimiter>custom> ( > left-most delimiter (column 1.2 now becomes column 1.2.1)
Select new column and delete (it deletes the (USD) because I don’t need it)
Select column 1.2.1>Transform>Fill>Fill down
Select column 1.1>filter>filter out nulls
Select column 1.1>Add Column>Duplicate Column>reorder column in third position (before Account Name)

I’m not sure how to take column 1.1 and do the two following:

Fill down the employee number (the six digit value) over the values there and stopping at the next employee number
Filter out TOTALS (USD) and all its values (rows 26-36 in my PQ Table2). I think it might be just easier to just define the table without them to avoid dealing with the issue.

Then do the same thing with column 1.1 – Copy as I did with 1.1 but get rid of the employee numbers and only have the account numbers

I assume once I fix both 1.1 and 1.1. – Copy I can filter out Account Name columns of nulls and just have account names.

I can just change the names for each month to it’s numerical equivalent as well as name the columns to employee #, employee name, and account #

And try as I might, I’m not sure transpose or make into a list for each month. Stacked onto of each other.

Thank you for any help!

jkpieterse · Answer

Jpalaci1&nbsp;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] &lt;&gt; 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] &lt;&gt; "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] &lt;&gt; null))
in
    #"Filtered Rows2"

sergeibaklan · Answer

Jpalaci1&nbsp;
One more variant
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Kept First Rows" = Table.FirstN(
        Source,
        List.PositionOf(Source[Column1],"TOTALS (USD)")
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Total"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] &lt;&gt; null)),
    #"Replaced Value1" = Table.ReplaceValue(
        #"Filtered Rows",
        each [Column1],
        each
            if Value.Type([Column1]) = type text
            then [Column1]
            else null,
            Replacer.ReplaceValue,{"Column1"}
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(
        Table.TransformColumnTypes(
            #"Replaced Value1",
            {{"Column1", type text}}, "en-US"),
            "Column1",
            Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false
        ),
        {"Employee Number", "Name"}
    ),
    #"Replaced Value" = Table.ReplaceValue(
        #"Split Column by Delimiter",
        " (USD)","",
        Replacer.ReplaceText,{"Name"}
    ),
    #"Filled Down" = Table.FillDown(
        #"Replaced Value",
        {"Employee Number", "Name"}
    )
in
    #"Filled Down"

jpalaci1 · Answer

JKPieterse&nbsp;&nbsp;Thank you so much! this really helps!&nbsp;Two questions:1) How do I stack each column onto each other/transpose the list? For example:&nbsp;Employee &nbsp; &nbsp; Amount &nbsp; &nbsp;MonthEmployee 1 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Employee 2 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Employee 3 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Employee 1 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Employee 2 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Employee 3 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Employee 1 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Employee 2 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Employee 3 &nbsp; &nbsp; &nbsp; x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;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(&nbsp;Thank you again so much!&nbsp;

jpalaci1 · Answer

Another question:&nbsp;If I have questions about my M Code it's best to copy what I got so far in Advanced Editor and share that?&nbsp;Thank you!

sergeibaklan · Answer

Jpalaci1&nbsp;
JKPieterse&nbsp;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.
&nbsp;
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.
&nbsp;
3) It's always better to attach the file, scripts within posts are more for other people who will check for similar solutions.

Forum Discussion

Stuck in PQ and not sure how to achieve next steps

7 Replies

Resources