Forum Discussion

Ramh84's avatar
Ramh84
Frequent Visitor
2 years ago
Solved

Recurrence Problem in Power BI

Good day everyone,

I am facing some techinical difficulities to move from the input table below, to the desired table.

My goal is to print the recurrence of the each Merged item (Merged Header) as of the selected date in the BI report.

For example, 

If I am selecting a specific date at whcih the Merged item is available e.g., 5-Feb, Marry, A1, 14548, I should have the recurrence

showing 2. In other means the power query should give me the the recurrence of Marry, A1, 14548 since the begining of the year untill the selected date(if such record(Merged header) falls on that day.  

 

The reason that I am having a second merged header that includes the Date is because I am intending to link the recurrence header with another table that will have the same Merged.1 header in there. 
Here you can find the excel sheet 
Test file

 

I will really appreciate your help in solving this buzzle 🙂

 

 

 

Input

 

 

 

Desired output

 

 

 

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    You can group by 'Merged', choose 'all rows' and then add an index column starting at one for each nested table. Expand the nested tables with the index column and you will have your result.
    Example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRThikMxLpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"nestedTables", each _, type table [Column1=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"nestedTables", each Table.AddIndexColumn(_, "Recurrence", 1, 1, Int64.Type)}}),
    #"Expanded nestedTables" = Table.ExpandTableColumn(Custom1, "nestedTables", {"Recurrence"}, {"Recurrence"})
in
    #"Expanded nestedTables"

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    You can group by 'Merged', choose 'all rows' and then add an index column starting at one for each nested table. Expand the nested tables with the index column and you will have your result.
    Example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRThikMxLpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"nestedTables", each _, type table [Column1=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"nestedTables", each Table.AddIndexColumn(_, "Recurrence", 1, 1, Int64.Type)}}),
    #"Expanded nestedTables" = Table.ExpandTableColumn(Custom1, "nestedTables", {"Recurrence"}, {"Recurrence"})
in
    #"Expanded nestedTables"
  • Ramh84's avatar
    Ramh84
    Frequent Visitor
    2 years ago

    Really really appreciated your great support!

     

    It worked 100%!

    All what I needed to do before applying the changes in my actual BI file is to delete the Merged column after creating another one that is a result from merging the merged column with the date to have no duplicate records in the table.