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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mahra-in
Helper II
Helper II

How to Merge for Year and Greater Year

Hi All

 

Can you pelase sugget the solution for my problem below

 

I have 2 Tables

 

Status Table:

NameYearDateStatus
X20183-May-18Commit
Y20202-Jun-22Commit
Y202222-Sep-22Target
Z20221-Feb-22Target

 

Transaction Table:

NameYear
X2018
X2019
X2022
Y2020
Y2021
Y2022
Z2018
Z2020
Z2022

 

The Result Transaction Table to be

 

NameYearStatus
X2018Commit
X2019Commit
X2022Commit
Y2020Commit
Y2021Commit
Y2022Target
Z2018No Status
Z2020No Status
Z2022Target

 

Here you can see that,

for X, in 2018 we have status as "Commit" in Status Table which needs to be passed to X from 2018 till current year 2022 in Transaction Table

 

for Y, in 2020 we have "Commit" which needs to passed for 2020 & 2021 but for 2022 we need to pass Target since status chnaged as "Target" in 2022

 

for Z, in 2022 we have "Target" hence we need to pass "Target" for 2022 but for earlier years like 2018 & 2020 we dont have status hence we say "No Status"

 

I see this as a unique problem kindly help to resolve

 

Mahes r

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @mahra-in 

 

These are my transformation steps. Hope it helps. PBIX is attached at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTIyMLRQitWBcyyROEZGYE4khGOAzDFE5kCURSGbFoWsJwquLBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name", "Year"}, #"Status Table", {"Name", "Year"}, "Status Table", JoinKind.LeftOuter),
    #"Expanded Status Table" = Table.ExpandTableColumn(#"Merged Queries", "Status Table", {"Status"}, {"Status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Status Table",{{"Name", Order.Ascending}, {"Year", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"All Data", each _, type table [Name=nullable text, Year=nullable number, Status=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"All Data", each Table.FillDown(_,{"Status"})}),
    #"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"Status"}, {"Status"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All Data",null,"No Status",Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

vjingzhang_1-1670379863217.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
mahra-in
Helper II
Helper II

Thank you very much for your great support

v-jingzhang
Community Support
Community Support

Hi @mahra-in 

 

These are my transformation steps. Hope it helps. PBIX is attached at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTIyMLRQitWBcyyROEZGYE4khGOAzDFE5kCURSGbFoWsJwquLBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name", "Year"}, #"Status Table", {"Name", "Year"}, "Status Table", JoinKind.LeftOuter),
    #"Expanded Status Table" = Table.ExpandTableColumn(#"Merged Queries", "Status Table", {"Status"}, {"Status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Status Table",{{"Name", Order.Ascending}, {"Year", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"All Data", each _, type table [Name=nullable text, Year=nullable number, Status=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"All Data", each Table.FillDown(_,{"Status"})}),
    #"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"Status"}, {"Status"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All Data",null,"No Status",Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

vjingzhang_1-1670379863217.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors