Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s15
Helper III
Helper III

Addition assignment operator formula in Power BI

Hi guys,

 

I need to create a column that uses Addition assignment operator starting at "1999". I need to create such a column with 16 row (from 1999 - 2014). Does Power BI Formula support such a case? If not, can I just separate by a comma? (e.g = 1999, 2000,....)

For example:

x = 1999  
x += 1

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

Not sure if you are looking for DAX or Power Query.

 

In Power Query you can create a table from scratch or you can add a custom index column starting at 1999 with increments of 1.

I did both, resulting in the following code. The first line was added via the advanced editor; the second line was generated by choosing Add Column - Index Column - Custom:

 

let
    Source = Table.FromColumns({{1999..2014}},type table[FromScratch = Int64.Type]),
    #"Added Index" = Table.AddIndexColumn(Source, "AddedAsCustomIndex", 1999, 1)
in
    #"Added Index"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

Not sure if you are looking for DAX or Power Query.

 

In Power Query you can create a table from scratch or you can add a custom index column starting at 1999 with increments of 1.

I did both, resulting in the following code. The first line was added via the advanced editor; the second line was generated by choosing Add Column - Index Column - Custom:

 

let
    Source = Table.FromColumns({{1999..2014}},type table[FromScratch = Int64.Type]),
    #"Added Index" = Table.AddIndexColumn(Source, "AddedAsCustomIndex", 1999, 1)
in
    #"Added Index"
Specializing in Power Query Formula Language (M)

Hi  @MarcelBeug

 

 I see you are creating a nested list with double curly brackets and Table.FromColumns will not work with single set of curly brackets.

 

Does it mean that wrapping lists in another set of curly brackets allows PQ functions to recognize values as "official" columns ?


{{1999..2014}}

 

Thanks, N

Not exactly.

Wrapping a list like {1999..2014} in another set of curly brackets, creates a list with nested lists.

Suppose you would have
List1999_2014 = {1999..2014}

then you can create a list of lists with
DuplicateList = (List1999_2014,List1999_2014}

no need for inner curly brackets, as List1999_2014 is already a list.

This would be equivalent with:

DuplicateList = ({1999..2014},{1999..2014}}

 

Function Table.FromColumns requires a list of lists: each nested list will be a table column.

So Table.FromColumns(DuplicateList) would create a table with 2 columns.

 

I would formulate it as follows:

the function Table.FromColumns recognizes DuplicateList as a list of lists and creates a table with "official" columns.

 

 

Specializing in Power Query Formula Language (M)

Thank you @MarcelBeug That works!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.