cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris1300
Frequent Visitor

How to add new rows based on input table?

Hello,

 

I have a starting table as input. It has 3 colomuns. Please see below. I would like to get a new table that will have more rows based on original table. See table below, Each Date# has every Activity# and every Source#. In this example, there are 4 dates, 2 activities, 2 sources. Therefore the final table row count is 4*2*2 = 16.

 

Can this be done in DAX?

 

StartDateActivityNameSource
Date1Activity 1Source1
Date2Activity 2Source2
Date3  
Date4  

 

The final table result should be:

 

StartDateActivityNameSource
Date1Activity 1Source1
Date1Activity 2Source1
Date1Activity 1Source2
Date1Activity 2Source2
Date2Activity 1Source1
Date2Activity 2Source1
Date2Activity 1Source2
Date2Activity 2Source2
Date3Activity 1Source1
Date3Activity 2Source1
Date3Activity 1Source2
Date3Activity 2Source2
Date4Activity 1Source1
Date4Activity 2Source1
Date4Activity 1Source2
Date4Activity 2Source2
1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY30TcyVNJRckwuySzLLKlUAHGC80uLklMNlWJ1opVM9Q31jYyQVRjBVRiBVZjpGxkDEVAULmEMljDXNzTSNzKBSCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, ActivityName = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"ActivityName", type text}, {"Source", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"StartDate", "ActivityName", "Source"}),
    StartDateList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","StartDate"))),
    ActivityNameList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","ActivityName"))),
    SourceList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","Source"))),
    StartDateListCount = List.Count(StartDateList),
    ActivityNameListCount = List.Count(ActivityNameList),
    SourceListCount = List.Count(SourceList),
    ElementsCount = StartDateListCount*ActivityNameListCount*SourceListCount,
    StartDateListRepeatCount = ElementsCount/StartDateListCount,
    GenStartDateList = List.Combine(List.Transform(StartDateList,(x)=>List.Repeat({x},StartDateListRepeatCount))),
    SourceListRepeatCount = ElementsCount/SourceListCount,
    GenSourceListTemp = List.Combine(List.Transform(SourceList,(x)=>List.Repeat({x},ActivityNameListCount))),
    GenSourceList = List.Combine(List.Repeat({GenSourceListTemp},ElementsCount/List.Count(GenSourceListTemp))),
    ActivityNameListRepeatCount = ElementsCount/ActivityNameListCount,
    GenActivityNameListTemp = List.Combine(List.Repeat({ActivityNameList},SourceListCount)),
    GenActivityNameList = List.Combine(List.Repeat({GenActivityNameListTemp},ElementsCount/List.Count(GenActivityNameListTemp))),
    Result = Table.FromColumns({GenStartDateList} & {GenActivityNameList} & {GenSourceList},Table.ColumnNames(Source))
in
    Result

 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY30TcyVNJRckwuySzLLKlUAHGC80uLklMNlWJ1opVM9Q31jYyQVRjBVRiBVZjpGxkDEVAULmEMljDXNzTSNzKBSCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, ActivityName = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"ActivityName", type text}, {"Source", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"StartDate", "ActivityName", "Source"}),
    StartDateList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","StartDate"))),
    ActivityNameList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","ActivityName"))),
    SourceList = List.Buffer(List.RemoveNulls(Table.Column(#"Replaced Value","Source"))),
    StartDateListCount = List.Count(StartDateList),
    ActivityNameListCount = List.Count(ActivityNameList),
    SourceListCount = List.Count(SourceList),
    ElementsCount = StartDateListCount*ActivityNameListCount*SourceListCount,
    StartDateListRepeatCount = ElementsCount/StartDateListCount,
    GenStartDateList = List.Combine(List.Transform(StartDateList,(x)=>List.Repeat({x},StartDateListRepeatCount))),
    SourceListRepeatCount = ElementsCount/SourceListCount,
    GenSourceListTemp = List.Combine(List.Transform(SourceList,(x)=>List.Repeat({x},ActivityNameListCount))),
    GenSourceList = List.Combine(List.Repeat({GenSourceListTemp},ElementsCount/List.Count(GenSourceListTemp))),
    ActivityNameListRepeatCount = ElementsCount/ActivityNameListCount,
    GenActivityNameListTemp = List.Combine(List.Repeat({ActivityNameList},SourceListCount)),
    GenActivityNameList = List.Combine(List.Repeat({GenActivityNameListTemp},ElementsCount/List.Count(GenActivityNameListTemp))),
    Result = Table.FromColumns({GenStartDateList} & {GenActivityNameList} & {GenSourceList},Table.ColumnNames(Source))
in
    Result

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors