Denormalize automaticly a datadate

Sorry, I can understand than it's not understable :=) 

Probably better with the image below. Tell me if it's better even if you don't have any solution. Thanks. 

Laurent

Hi lhdp 

Check my pbix below, if you have any problem, feel free to let me know.

Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjEwMDy0QElHyTE9Na8Ewjy0wLWwNLMAxjHUN9Q3MjAyAHPD/Q2VYnXQ9Rkh64NxjFD1GcH0QeQDSnPhNpgaGMD0GBhagJkmEDEMLTDDzZC1WIKZQB1gLbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Data Code " = _t, #" TX1_VALUE " = _t, TX2_VALUE = _t, TX3_VALUE = _t, TX4_VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data Code ", type text}, {" TX1_VALUE ", type text}, {"TX2_VALUE", type text}, {"TX3_VALUE", type text}, {"TX4_VALUE", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Data Code "}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Data Code ", Text.Trim, type text}, {"Attribute", Text.Trim, type text}, {"Value", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Data Code ", Text.Clean, type text}, {"Attribute", Text.Clean, type text}, {"Value", Text.Clean, type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Cleaned Text", {"Data Code ", "Attribute"}, Table2, {"Data Code", "Field"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"FieldNature"}, {"Table2.FieldNature"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Data Code ", "Table2.FieldNature"}, { {"rank", each _, type table [#"Data Code "=text, Value=text, Table2.FieldNature=text]}}),
    RankFunction = (tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Value", Order.Ascending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
     in
      AddIndex,
    AddedRank = Table.TransformColumns(#"Grouped Rows", {"rank", each RankFunction(_)}),
    #"Expanded rank" = Table.ExpandTableColumn(AddedRank, "rank", {"Value", "Rank"}, {"rank.Value", "rank.Rank"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded rank",{{"rank.Value", "Value"}, {"rank.Rank", "Rank"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Data Code "}, #"Data Code", {"Data Code"}, "Data Code", JoinKind.LeftOuter),
    #"Expanded Data Code" = Table.ExpandTableColumn(#"Merged Queries1", "Data Code", {"Index"}, {"Data Code.Index"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Data Code", "new index", each Text.Combine({Text.From([Data Code.Index], "en-US"), Text.From([Rank], "en-US")}, "_"), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Rank", "Data Code.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Table2.FieldNature]), "Table2.FieldNature", "Value")
in
    #"Pivoted Column"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.