Pulling only certain text criteria into another cell

[KayCee]

Hello,

I'm trying to create a spreadsheet that can automatically generate meeting dates in a year. I have the current spreadsheet below where I have a dropdown option for column D and a formula in Column E of =LET(s,SWITCH(D2,"Annual",12,"Semi-Annual",6,"Tri-Annual",4),IF(D2="","",TEXTJOIN(", ",,TEXT(EDATE(B2,SEQUENCE(12/s,,s,s)),"mmmm 'y")))). For Column A, I need to be able to pull the months generated in column E and create duplicates for a household if there is more than one month listed in E. This would allow me to sort the spreadsheet my months so we can see who we need to meet with in a given month. Is it possible to do this?

 

[severynm]

So what exactly is the difference between col A and E? Unless I'm missing something, wouldn't they contain the same information?

One option would be to use a text filter on the Next Meetings column for rows that contain the month and year you're looking at.

 

[KayCee]

So what exactly is the difference between col A and E? Unless I'm missing something, wouldn't they contain the same information?

One option would be to use a text filter on the Next Meetings column for rows that contain the month and year you're looking at.
View attachment 57202

They do contain the same information just in a different way, column A would only have one month listed where column E may have two or three. I need to be able to sort this spreadsheet by month and group each month in column A so we can see who all needs to be scheduled for a particular month.

 

[severynm]

column A would only have one month listed where column E may have two or three

If A only has one month, what criteria are you using to determine which date should go there out of the dates in col E? Soonest date? Latest date? Something else?

 

[KayCee]

If A only has one month, what criteria are you using to determine which date should go there out of the dates in col E? Soonest date? Latest date? Something else?

It's not necessarily soonest or latest, I just need each month to pull over so it ends up looking like the table below and then I can sort by review month. For the list below, I just inserted rows and copy and pasted and then typed the review month manually. But obviously that wouldn't be very time efficient to do for everyone.

 

[severynm]

Ohhhhhhh. You want one row for each date in the Next Meeting column for each account. That involves a fair bit of data transformation - the way I would approach that is to load the data through Power Query and let it do the transformations for us. From your your table of data, on the Data ribbon tab → Get data From Table/Range. That creates a new query based on your data that you can then start to manipulate.

Basically, you need to split the Next Meetings column by a comma delimiter, and then unpivot the new columns we just created back into one single column. Lastly, trim and clean to get rid of any extra spaces lying around. If you start with the code below and put in in the advanced editor, you'll need to customize the name of the source table. It also assumes tri-annual is the most number of Next Meetings you have - if you require more, you'll have to tweak the Split and Unpivot steps. Otherwise, it should be a good starting point.

let
    //customize the Source step for your table name
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],  
    #"Changed Type All Cols" = Table.TransformColumnTypes(Source,{{"Last Meeting", type date}, {"Account Name", type text}, {"Meeting Frequency", type text}, {"Next Meeting(s)", type text}}),
    #"Duplicated Col 'Next Meetings'" = Table.DuplicateColumn(#"Changed Type All Cols", "Next Meeting(s)", "Next Meeting(s) - temp"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Col 'Next Meetings'", "Next Meeting(s) - temp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Next Meetings.1", "Next Meetings.2", "Next Meetings.3"}),
    #"Unpivoted Split Cols" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Last Meeting", "Account Name", "Meeting Frequency", "Next Meeting(s)"}, "Attribute", "Review Month"),
    #"Removed Col 'Attribute'" = Table.RemoveColumns(#"Unpivoted Split Cols",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Col 'Attribute'",{{"Review Month", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Review Month", Text.Clean, type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Cleaned Text",{"Review Month", "Last Meeting", "Account Name", "Meeting Frequency", "Next Meeting(s)"})
in
    #"Reordered Columns"

The result is a new table that looks exactly like the sample you just posted. The whole purpose of PQ is to make data transformations without changing the original data, so if data in the original table changes or is updated, you can easily refresh the query by refreshing from the Data or Query ribbon tab or right clicking in the query table. All of the steps we defined will automatically be rerun with the data in the source table.

 

[KayCee]

Ohhhhhhh. You want one row for each date in the Next Meeting column for each account. That involves a fair bit of data transformation - the way I would approach that is to load the data through Power Query and let it do the transformations for us. From your your table of data, on the Data ribbon tab → Get data From Table/Range. That creates a new query based on your data that you can then start to manipulate.

Basically, you need to split the Next Meetings column by a comma delimiter, and then unpivot the new columns we just created back into one single column. Lastly, trim and clean to get rid of any extra spaces lying around. If you start with the code below and put in in the advanced editor, you'll need to customize the name of the source table. It also assumes tri-annual is the most number of Next Meetings you have - if you require more, you'll have to tweak the Split and Unpivot steps. Otherwise, it should be a good starting point.

let
    //customize the Source step for your table name
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    #"Changed Type All Cols" = Table.TransformColumnTypes(Source,{{"Last Meeting", type date}, {"Account Name", type text}, {"Meeting Frequency", type text}, {"Next Meeting(s)", type text}}),
    #"Duplicated Col 'Next Meetings'" = Table.DuplicateColumn(#"Changed Type All Cols", "Next Meeting(s)", "Next Meeting(s) - temp"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Col 'Next Meetings'", "Next Meeting(s) - temp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Next Meetings.1", "Next Meetings.2", "Next Meetings.3"}),
    #"Unpivoted Split Cols" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Last Meeting", "Account Name", "Meeting Frequency", "Next Meeting(s)"}, "Attribute", "Review Month"),
    #"Removed Col 'Attribute'" = Table.RemoveColumns(#"Unpivoted Split Cols",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Col 'Attribute'",{{"Review Month", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Review Month", Text.Clean, type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Cleaned Text",{"Review Month", "Last Meeting", "Account Name", "Meeting Frequency", "Next Meeting(s)"})
in
    #"Reordered Columns"

The result is a new table that looks exactly like the sample you just posted. The whole purpose of PQ is to make data transformations without changing the original data, so if data in the original table changes or is updated, you can easily refresh the query by refreshing from the Data or Query ribbon tab or right clicking in the query table. All of the steps we defined will automatically be rerun with the data in the source table.
View attachment 57247

So I entered the code and had to change the column names a bit to match my spreadsheet but I keep getting a Token Comma expected code and I cannot figure out why. This is what I put in and the red bold section is where I'm getting the error. This is the first time I've ever worked with a Query.

let
Source = Excel.CurrentWorkbook(){[Name="Tim"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Next Meeting", type text}, {"Account Owner ↑", type text}, {"Account Name", type text}, {"Account Record Type", type text}, {"Classification", type text}, {"Last Meeting", type any}, {"Meeting Frequency", type text}, {"Meeting Months", type text}, {"TAM AUM", Int64.Type}, {"2022 Meeting", type datetime}}),
#"Changed Type All Cols" = Table.TransformColumnTypes(#"Promoted Headers",{{"Next Meeting", type text}, {"Account Owner ↑", type text}, {"Account Name", type text}, {"Account Record Type", type text}, {"Classification", type text}, {"Last Meeting", type any}, {"Meeting Frequency", type text}, {"Meeting Months", type text}, {"TAM AUM", Int64.Type}, {"2022 Meeting", type datetime}})
#"Duplicated Col 'Meeting Months'" = Table.DuplicateColumn(#"Changed Type All Cols",{{"Meeting Months"}, {"Meeting Months - temp"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Col 'Meeting Months)'", "Meeting Months - temp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Meeting Months.1", "Meeting Months.2", "Meeting Months.3"}),
#"Unpivoted Split Cols" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Last Meeting", "Account Name", "Meeting Frequency", "Meeting Months"}, "Attribute", “Next Meeting"),
#"Removed Col 'Attribute'" = Table.RemoveColumns(#"Unpivoted Split Cols",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Col 'Attribute'",{{"Next Meeting", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Next Meeting", Text.Clean, type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Cleaned Text",{"Next Meeting", "Last Meeting", "Account Name", "Meeting Frequency", "Meeting Months"})
in
#"Reordered Columns"

 

[severynm]

Yep - you're missing a comma at the end of the line right above it. Additionally, watch out on the "Umpivoted Split Cols" step too - towards the end there's an invalid identifier of an apostrophe lookalike that needs to be replaced.

 

[KayCee]

Yep - you're missing a comma at the end of the line right above it. Additionally, watch out on the "Umpivoted Split Cols" step too - towards the end there's an invalid identifier of an apostrophe lookalike that needs to be replaced.

thank you for that! It's those little things that get overlooked sometimes. I fixed the code and it created a new sheet but it's still not producing anything in the Next Meeting column. Below is what I'm seeing - the green spreadsheet is off the new query and the blue is the original. Below that is the code.

let
Source = Excel.CurrentWorkbook(){[Name="Tim"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Next Meeting", type any}, {"Account Owner ↑", type text}, {"Account Name", type text}, {"Account Record Type", type text}, {"Classification", type text}, {"Last Meeting", type any}, {"Meeting Frequency", type text}, {"Meeting Months", type text}, {"TAM AUM", Int64.Type}, {"2022 Meeting", type datetime}}),
#"Changed Type All Cols" = Table.TransformColumnTypes(#"Promoted Headers",{{"Next Meeting", type text}, {"Account Owner ↑", type text}, {"Account Name", type text}, {"Account Record Type", type text}, {"Classification", type text}, {"Last Meeting", type any}, {"Meeting Frequency", type text}, {"Meeting Months", type text}, {"TAM AUM", Int64.Type}, {"2022 Meeting", type datetime}}),
#"Duplicated Col 'Meeting Months'" = Table.DuplicateColumn(#"Changed Type All Cols",{{"Meeting Months"}, {"Meeting Months - temp"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Col 'Meeting Months", "Meeting Months - temp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Meeting Months.1", "Meeting Months.2", "Meeting Months.3"}),
#"Unpivoted Split Cols" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Last Meeting", "Account Name", "Meeting Frequency", "Meeting Months"}, "Attribute", "Next Meeting"),
#"Removed Col 'Attribute'" = Table.RemoveColumns(#"Unpivoted Split Cols",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Col 'Attribute'",{{"Next Meeting", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Next Meeting", Text.Clean, type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Cleaned Text",{"Next Meeting", "Last Meeting", "Account Name", "Meeting Frequency", "Meeting Months"})
in
#"Changed Type"

I know you used #"Reordered Columns" for the in portion but every time I do that, I get the expression error below and I can't figure out for the life of me why it's not finding the column.

 

[severynm]

Whatever comes after the in is just the name of the last step used in the query. The unicode character (the arrow) shouldn't cause issues as I've used them as column headers in the past too, but try removing it to see if that fixes things.