topic Re: List.Transform referencing column returns different data in Power Query

List.Transform referencing column returns different data

foged — Mon, 21 Sep 2020 10:18:47 GMT

I am building a power query adding a list of dates using List.Transform. I reference an existing column using (thisrow) inline custom function, however the populated list, referencing the same column returns random data:

let
Source = OData.Feed("....", null, [Implementation="2.0"]),
#"Add Custom" = Table.AddColumn(Source, "ReportDate", each [PeriodStart], type date),
#"Add Months" = Table.AddColumn(#"Add Custom", "Months", each (Duration.Days([PeriodEnd]-[PeriodStart]))),
#"MonthList" = Table.AddColumn(
#"Add Months", "DateList", 
(ThisRow) => List.Transform(
{0..3}, 
each 
ThisRow[Months] )
, type list
)
in
MonthList

When I look at the List generated (0..3) each item in the list, does not corrospond with the data stored in the ReportDate column. Any idea?

Re: List.Transform referencing column returns different data

Jimmy801 — Mon, 21 Sep 2020 10:28:43 GMT

Hello foged

your code is saying, that you are a creating a list with 4 items, and all items have the value of the column "months", not with the column "ReportDate"

What exactly should this list contain?

Jimmy

Re: List.Transform referencing column returns different data

foged — Mon, 21 Sep 2020 10:37:52 GMT

Hi Jimmy, thanks for reaching the item. My sample (was posted) was referencing both [months] (number column) to test and also the [reportdate] (date column).

Here is the sample view when referencing the [ReportDate] column.

Re: List.Transform referencing column returns different data

Jimmy801 — Tue, 22 Sep 2020 05:33:01 GMT

Hello foged

the code you posted will not create this result. It will create a list with 4 items and each will get the value of the "months"-column. What exactly you need?

Jimmy

Re: List.Transform referencing column returns different data

foged — Tue, 22 Sep 2020 05:46:57 GMT

Hi Jimmy,

That is the whole point - the value of "months" column is various from the rows of the table, not actually the value of the (ThisRow) parameter. I've narrowed it down by sorting the list from A-Z on the date column, which then produces the right results, but I've never had to do this before. Seems like a bug in Power BI desktop.

Re: List.Transform referencing column returns different data

Jimmy801 — Tue, 22 Sep 2020 06:40:30 GMT

Hello foged

try this code if now its working

let Source = OData.Feed("....", null, [Implementation="2.0"]), #"Add Custom" = Table.AddColumn(Source, "ReportDate", each [PeriodStart], type date), #"Add Months" = Table.Buffer(Table.AddColumn(#"Add Custom", "Months", each (Duration.Days([PeriodEnd]-[PeriodStart])))), #"MonthList" = Table.AddColumn( #"Add Months", "DateList", (ThisRow) => List.Transform( {0..3}, each ThisRow[Months] ) , type list ) in MonthList

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy