A table of multiple values was supplied where a single value was expected

chrisgreenslade · March 11, 2021, 4:17pm

Melissa · March 11, 2021, 4:23pm

yeah post the full M script please

chrisgreenslade · March 11, 2021, 4:31pm

= Table.AddColumn(#“Changed Type1”, “Custom”, each Table.AddColumn(“Purchase Duration”, each let
pTerminal = [Terminal ID],
pDayName = [Purchase Start Day Name],
pStartDate = Date.From([#“Purchase Start Date & Time”]),
pStartTime = Number.From(Time.From([#“Purchase Start Date & Time”])),
pAmount = Number.From( [Purchase Amount]),
rTable = Table.SelectRows( BufferTariffs, (BT)=>
BT[Terminal ID] = pTerminal and
BT[Day Name] = pDayName and
BT[Tariff Start Date] <= pStartDate and
BT[Tariff End Date] >= pStartDate and
BT[Tariff Amount Start] <= pAmount and
BT[Tariff Amount End] >= pAmount and
( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime )
)
in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform(rTable[Tariff Duration], each Text.From(_)), ", ") else null, type text))

Melissa · March 11, 2021, 4:33pm

Need the full M script of your Purchases query - thanks

chrisgreenslade · March 11, 2021, 4:35pm

How do I get that? It was queried in steps.

Sorry…realised in advanced editor.

chrisgreenslade · March 11, 2021, 4:36pm

let
Source = Table.Combine({Flowbird, IPS, MiPermit}),
#“Replaced Value” = Table.ReplaceValue(Source,null,“MiPermit”,Replacer.ReplaceValue,{“Payment Type”}),
#“Inserted Time” = Table.AddColumn(#“Replaced Value”, “Time”, each DateTime.Time([#“Purchase Start Date & Time”]), type time),
#“Inserted Day Name” = Table.AddColumn(#“Inserted Time”, “Day Name”, each Date.DayOfWeekName([#“Purchase Start Date & Time”]), type text),
#“Reordered Columns” = Table.ReorderColumns(#“Inserted Day Name”,{“Terminal ID”, “Purchase Start Date & Time”, “Time”, “Day Name”, “Purchase Amount”, “Payment Type”, “Purchase End Date & Time”}),
#“Renamed Columns” = Table.RenameColumns(#“Reordered Columns”,{{“Time”, “Purchase Start Time”}, {“Day Name”, “Purchase Start Day Name”}}),
#“Inserted Date” = Table.AddColumn(#“Renamed Columns”, “Date”, each DateTime.Date([#“Purchase Start Date & Time”]), type date),
#“Renamed Columns1” = Table.RenameColumns(#“Inserted Date”,{{“Date”, “Purchase Start Date”}}),
#“Reordered Columns1” = Table.ReorderColumns(#“Renamed Columns1”,{“Terminal ID”, “Purchase Start Date & Time”, “Purchase Start Date”, “Purchase Start Time”, “Purchase Start Day Name”, “Purchase Amount”, “Payment Type”, “Purchase End Date & Time”}),
#“Duplicated Column” = Table.DuplicateColumn(#“Reordered Columns1”, “Purchase End Date & Time”, “Purchase End Date & Time - Copy”),
#“Split Column by Delimiter” = Table.SplitColumn(Table.TransformColumnTypes(#“Duplicated Column”, {{“Purchase End Date & Time - Copy”, type text}}, “en-GB”), “Purchase End Date & Time - Copy”, Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {“Purchase End Date & Time - Copy.1”, “Purchase End Date & Time - Copy.2”}),
#“Changed Type” = Table.TransformColumnTypes(#“Split Column by Delimiter”,{{“Purchase End Date & Time - Copy.1”, type text}, {“Purchase End Date & Time - Copy.2”, type text}}),
#“Renamed Columns2” = Table.RenameColumns(#“Changed Type”,{{“Purchase End Date & Time - Copy.1”, “Purchase End Date”}, {“Purchase End Date & Time - Copy.2”, “Purchase End Time”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns2”,{{“Purchase End Date”, type date}, {“Purchase End Time”, type time}}),
BufferTariffs = Table.Buffer(Tariffs ),
#“Added Custom” = Table.AddColumn(“Changed Type1”, “Custom”, each Table.AddColumn(“Purchase Duration”, each let
pTerminal = [Terminal ID],
pDayName = [Purchase Start Day Name],
pStartDate = Date.From([#“Purchase Start Date & Time”]),
pStartTime = Number.From(Time.From([#“Purchase Start Date & Time”])),
pAmount = Number.From( [Purchase Amount]),
rTable = Table.SelectRows( BufferTariffs, (BT)=>
BT[Terminal ID] = pTerminal and
BT[Day Name] = pDayName and
BT[Tariff Start Date] <= pStartDate and
BT[Tariff End Date] >= pStartDate and
BT[Tariff Amount Start] <= pAmount and
BT[Tariff Amount End] >= pAmount and
( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime )
)
in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform(rTable[Tariff Duration], each Text.From(_)), ", ") else null, type text))
in
#“Added Custom”

Melissa · March 11, 2021, 4:50pm

Give this a go.

let
Source = Table.Combine({Flowbird, IPS, MiPermit}),
#"Replaced Value" = Table.ReplaceValue(Source,null,"MiPermit",Replacer.ReplaceValue,{"Payment Type"}),
#"Inserted Time" = Table.AddColumn(#"Replaced Value", "Time", each DateTime.Time([#"Purchase Start Date & Time"]), type time),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Time", "Day Name", each Date.DayOfWeekName([#"Purchase Start Date & Time"]), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Day Name",{"Terminal ID", "Purchase Start Date & Time", "Time", "Day Name", "Purchase Amount", "Payment Type", "Purchase End Date & Time"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Time", "Purchase Start Time"}, {"Day Name", "Purchase Start Day Name"}}),
#"Inserted Date" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([#"Purchase Start Date & Time"]), type date),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Date",{{"Date", "Purchase Start Date"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Terminal ID", "Purchase Start Date & Time", "Purchase Start Date", "Purchase Start Time", "Purchase Start Day Name", "Purchase Amount", "Payment Type", "Purchase End Date & Time"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Purchase End Date & Time", "Purchase End Date & Time - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Purchase End Date & Time - Copy", type text}}, "en-GB"), "Purchase End Date & Time - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"Purchase End Date & Time - Copy.1", "Purchase End Date & Time - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Purchase End Date & Time - Copy.1", type text}, {"Purchase End Date & Time - Copy.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Purchase End Date & Time - Copy.1", "Purchase End Date"}, {"Purchase End Date & Time - Copy.2", "Purchase End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Purchase End Date", type date}, {"Purchase End Time", type time}}),
BufferTariffs = Table.Buffer(Tariffs ),
#"Added Custom" = Table.AddColumn( "Changed Type1", "Purchase Duration", each let
    pTerminal = [Terminal ID],
    pDayName = [Purchase Start Day Name],
    pStartDate = Date.From([#"Purchase Start Date & Time"]),
    pStartTime = Number.From(Time.From([#"Purchase Start Date & Time"])),
    pAmount = Number.From( [Purchase Amount]),
    rTable = Table.SelectRows( BufferTariffs, (BT)=>
        BT[Terminal ID] = pTerminal and
        BT[Day Name] = pDayName and
        BT[Tariff Start Date] <= pStartDate and
        BT[Tariff End Date] >= pStartDate and
        BT[Tariff Amount Start] <= pAmount and
        BT[Tariff Amount End] >= pAmount and
        (( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
        ( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime ))
    )
    in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform( rTable[Tariff Duration], each Text.From(_)), ", ") else null, type text)
in
#"Added Custom"

chrisgreenslade · March 11, 2021, 4:52pm

As a step for a customised column? Or new query?

chrisgreenslade · March 11, 2021, 4:56pm

Melissa · March 11, 2021, 4:59pm

Forgot to remove the last bracket, I apologise, please try again
Select everything in the Advanced Editor and replace it with this.

let
Source = Table.Combine({Flowbird, IPS, MiPermit}),
#"Replaced Value" = Table.ReplaceValue(Source,null,"MiPermit",Replacer.ReplaceValue,{"Payment Type"}),
#"Inserted Time" = Table.AddColumn(#"Replaced Value", "Time", each DateTime.Time([#"Purchase Start Date & Time"]), type time),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Time", "Day Name", each Date.DayOfWeekName([#"Purchase Start Date & Time"]), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Day Name",{"Terminal ID", "Purchase Start Date & Time", "Time", "Day Name", "Purchase Amount", "Payment Type", "Purchase End Date & Time"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Time", "Purchase Start Time"}, {"Day Name", "Purchase Start Day Name"}}),
#"Inserted Date" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([#"Purchase Start Date & Time"]), type date),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Date",{{"Date", "Purchase Start Date"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Terminal ID", "Purchase Start Date & Time", "Purchase Start Date", "Purchase Start Time", "Purchase Start Day Name", "Purchase Amount", "Payment Type", "Purchase End Date & Time"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Purchase End Date & Time", "Purchase End Date & Time - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Purchase End Date & Time - Copy", type text}}, "en-GB"), "Purchase End Date & Time - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"Purchase End Date & Time - Copy.1", "Purchase End Date & Time - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Purchase End Date & Time - Copy.1", type text}, {"Purchase End Date & Time - Copy.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Purchase End Date & Time - Copy.1", "Purchase End Date"}, {"Purchase End Date & Time - Copy.2", "Purchase End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Purchase End Date", type date}, {"Purchase End Time", type time}}),
BufferTariffs = Table.Buffer(Tariffs ),
#"Added Custom" = Table.AddColumn( "Changed Type1", "Purchase Duration", each let
    pTerminal = [Terminal ID],
    pDayName = [Purchase Start Day Name],
    pStartDate = Date.From([#"Purchase Start Date & Time"]),
    pStartTime = Number.From(Time.From([#"Purchase Start Date & Time"])),
    pAmount = Number.From( [Purchase Amount]),
    rTable = Table.SelectRows( BufferTariffs, (BT)=>
        BT[Terminal ID] = pTerminal and
        BT[Day Name] = pDayName and
        BT[Tariff Start Date] <= pStartDate and
        BT[Tariff End Date] >= pStartDate and
        BT[Tariff Amount Start] <= pAmount and
        BT[Tariff Amount End] >= pAmount and
        (( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
        ( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime ))
    )
    in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform( rTable[Tariff Duration], each Text.From(_)), ", ") else null, type text)
in
#"Added Custom"

chrisgreenslade · March 11, 2021, 5:04pm

Melissa · March 11, 2021, 5:06pm

It’s missing the # in front of the name…

so where it says
#“Added Custom” = Table.AddColumn( “Changed Type1”,

change that into
#“Added Custom” = Table.AddColumn( #“Changed Type1”,

chrisgreenslade · March 11, 2021, 5:22pm

I’ve actually lost track of what I am meant to be doing.

What do I put?

Melissa · March 11, 2021, 5:30pm

Past this into the formula section, you’ve depicted.

let
    pTerminal = [Terminal ID],
    pDayName = [Purchase Start Day Name],
    pStartDate = Date.From([#"Purchase Start Date & Time"]),
    pStartTime = Number.From(Time.From([#"Purchase Start Date & Time"])),
    pAmount = Number.From( [Purchase Amount]),
    rTable = Table.SelectRows( BufferTariffs, (BT)=>
        BT[Terminal ID] = pTerminal and
        BT[Day Name] = pDayName and
        BT[Tariff Start Date] <= pStartDate and
        BT[Tariff End Date] >= pStartDate and
        BT[Tariff Amount Start] <= pAmount and
        BT[Tariff Amount End] >= pAmount and
        (( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
        ( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
          Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime ))
    )
    in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform( rTable[Tariff Duration], each Text.From(_)), ", ") else null

.

Don’t forget to check the formula bar afterwards and if this step references BufferTarrifs as first argument in Table.AddColumns you need to change that into the step before that #“Changed Type1”

chrisgreenslade · March 11, 2021, 5:33pm

I don’t know what is going on!

Melissa · March 11, 2021, 5:36pm

…

chrisgreenslade · March 11, 2021, 5:51pm

I don’t know what that means. So I go to Tariffs query and do what?

Tariffs query

Melissa · March 11, 2021, 5:53pm

Yes and double click the column header for your [Terminal ID] column
and remove the non printable char at the end of the name

chrisgreenslade · March 11, 2021, 5:55pm

Is that as Insert Step? I am not a computer scientist. Merely a PhD Maths Student who has never had to use code, unless at undergraduate.

Melissa · March 11, 2021, 5:57pm

Make sure you have the last step selected in that Query before you change the name
A new Rename Column step will be added