Hi,
I have a query that calculates the number of hours difference between two date and time fields. There are then three conditional columns added that indicate a time band that the difference falls into. The time bands used in the conditional columns are driven from values entered in a table in the spreadsheet. These values can therefore be changed in the table and the conditional bandings are calculated accordingly.
What I would like to do if possible is change the headings of the conditional columns to show the values entered.
I have attached an example file. The query looks at the Table_Times and calculates the number of hours between the values in columns A and B. This is shown in the Query_Time_Diffs as "Subtraction". There are then the conditional columns that indicate Yes or No depending on whether the Subtraction value is within the range specified. In the attached column K returns Yes if the subtraction is between 24 and 48 hours, column L between 4 and 24 hours, and column M zero to 4 hours. The column headings are static and show these values currently.
If you change the values in the Table_Hours to say 5, 25, 49 and refresh the query the conditional columns will use the revised values but the column headers won't change. What I would like is that column K header would read "PO < 49hr before Start Date", column L "PO < 25hr before Start Date", column M PO "< 5hr before Start Date".
Is this possible?
Thanks in advance for any input.
Bax
Hi Bax,
You can modify your added column formulas like so:
= Table.AddColumn(#"Added Conditional Column7", "PO < " & Text.From(HourBand1) & "hr before Start Date", each if [Subtraction] > HourBand1 then "No" else if [Subtraction] >= 0 then "Yes" else "No")
Mynda
Hi Mynda,
That worked perfectly but I have now come across another related issue. I added a merged column to the query that pulls together the entries in the three time band columns. If you change the values in the Table_Hours the merged column fails.
I tried to update the column headers in the Inserted Merged Column step using the same method you prescribed as follows:
= Table.AddColumn(#"Added Conditional Column8", "Merged", each Text.Combine({[#"PO < " & Text.From(HourBand3) & "hr before Start Date"], [#"PO < " & Text.From(HourBand2) & "hr before Start Date"], [#"PO < " & Text.From(HourBand1) & "hr before Start Date"]}, ","), type text)
But this give the following error message:
Expression.SyntaxError: Invalid identifier.
I have attached an updated file with the merged column added.
Thanks
Bax
Hi Bax,
The query from the file you attached is refreshing without any errors.
Your formula:
= Table.AddColumn(#"Added Conditional Column8", "Merged", each Text.Combine({[#"PO < " & Text.From(HourBand3) & "hr before Start Date"], [#"PO < " & Text.From(HourBand2) & "hr before Start Date"], [#"PO < " & Text.From(HourBand1) & "hr before Start Date"]}, ","), type text)
Can be replaced with:
= Table.AddColumn(#"Added Conditional Column8", "Merged", (x)=> Text.Combine(List.Skip(Record.ToList(x),3), ","), type text)
It will combine all current row values, except first 3 which are removed (using List.Skip)
Hi Catalin,
Thanks that works well and another new technique to use. Very useful.
I did in fact manage to work out another way to do it. When I added the conditional columns I just added them with the default names suggested by PQ. So the first one is "Custom" as follows
= Table.AddColumn(#"Multiplied Column1", "Custom", each if [Subtraction] > HourBand3 then "No" else if [Subtraction] > HourBand2 then "Yes" else "No")
The others were then Custom.1 and Custom.2. I then merged the columns using these column headings. I then renamed the columns but inserted the dynamic element that Mynda shows me:
= Table.RenameColumns(#"Inserted Merged Column",{{"Custom", "PO < " & Text.From(HourBand3) & "hr before Start Date"}, {"Custom.1", "PO < " & Text.From(HourBand2) & "hr before Start Date"}, {"Custom.2", "PO < " & Text.From(HourBand1) & "hr before Start Date"}})
Both solutions work well.
Thanks for the help.
Bax
