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
richard-powerbi
Post Patron
Post Patron

Create extra rows based on amount but only keep some fields

How to get from table 1 to table 2? My main struggle is with creating those extra rows, but not filling the dates for the products that don't have a date yet.

Example:

I have table1, I want to get table 2 with the extra data (red).

If a product has amount 5, for example product D, and it only has 3 rows with dates, it means only 3 out of 5 have a date. I need to get a table with all 5 rows, but with only the 3 dates filled for the first 3 in the series: 1, 2 and 3. The remaining 4 and 5 should have no date: null.

 

table 1

id    product    amount    date    
1A5null
2B21-2-2020
2B22-2-2020
3V11-3-2020
4D51-4-2020
4D52-4-2020
4D53-4-2020
5E21-5-2020
6F11-6-2020
7G2null
8H3null

 

table 2

id    product    amount    date           index    
1A5null1
1A5null2
1A5null3
1A5null4
1A5null5
2B21-2-20201
2B22-2-20202
3V11-3-20201
4D51-4-20201
4D52-4-20202
4D53-4-20203
4D5null4
4D5null5
5E21-5-20201
5E2null2
6F11-6-20201
7G2null1
7G2null2
8H3null1
8H3null2
8H3null3
1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @richard-powerbi 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMgVorViVYyAjKcgBhEG+oa6RoZGBlgSBghSxgDBcJAqsE6jBESJkABF6jZhrom2CWMcEkYI0uABFzhrjJFSJgBBdzglpshJMyBAu5QHWABCyDDA2QuWCAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product = _t, amount = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"product", type text}, {"amount", Int64.Type}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "product", "amount"}, {{"Date2", each [date], type table [date=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each Table.FromColumns( {List.Combine({ [Date2], List.Repeat({null},[amount]-List.Count([Date2]))}) , List.Numbers(1,[amount])}, {"Date", "Index"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date2"}),
    #"Expanded Date" = Table.ExpandTableColumn(#"Removed Columns", "Date", {"Date", "Index"}, {"Date.1", "Index"})
in
    #"Expanded Date"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @richard-powerbi 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMgVorViVYyAjKcgBhEG+oa6RoZGBlgSBghSxgDBcJAqsE6jBESJkABF6jZhrom2CWMcEkYI0uABFzhrjJFSJgBBdzglpshJMyBAu5QHWABCyDDA2QuWCAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product = _t, amount = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"product", type text}, {"amount", Int64.Type}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "product", "amount"}, {{"Date2", each [date], type table [date=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each Table.FromColumns( {List.Combine({ [Date2], List.Repeat({null},[amount]-List.Count([Date2]))}) , List.Numbers(1,[amount])}, {"Date", "Index"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date2"}),
    #"Expanded Date" = Table.ExpandTableColumn(#"Removed Columns", "Date", {"Date", "Index"}, {"Date.1", "Index"})
in
    #"Expanded Date"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB genius! Thanks.

camargos88
Community Champion
Community Champion

Hi  @richard-powerbi ,

 

Sorry, I didn't get what you wanna do.

 

Can you explain it more ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 

I have table1, I want to get table 2 with the extra data (red).

If a product has amount 5, for example product D, and it only has 3 rows with dates, it means only 3 out of 5 have a date. I need to get a table with all 5 rows, but with only the 3 dates filled for the first 3 in the series: 1, 2 and 3. The remaining 4 and 5 should have no date: null.

 

I hope it's more clear now?

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.

Top Solution Authors
Top Kudoed Authors