Denormalize automaticly a datadate

[lhdp]

Hi the community,

I recieve a dataset very normalized with 2 differents kind of data : D001=Work order Data, D002 = Pumped Volume Data

Data Code   TX1_VALUE  TX2_VALUE TX3_VALUE TX4_VALUE

D001            Agent1         Equip1           1/1/2020    WO1

D001            Agent2         Equip2           2/1/2020    WO2

D002            Pump1         500                 2018          4500    

D002            Pump2         600                 2019          5000     

etc. with many different data code which represent very different types of data (and I have up to 10 TXn_VALUE fields)

For my exemple of course I receive another table with the parameters :

Data Code  Field            FieldNature

D001          TX1_VALUE  AgentCode

D001          TX2_VALUE  EquipmentCode

D001          TX3_VALUE  WorkOrderDate

D001          TX3_VALUE  WorkOrderNumber

D002          TX1_VALUE  PumpNumber

D002          TX2_VALUE  NomimalVolume

D002          TX3_VALUE  Year

D002          TX4_VALUE  PumpedVolume

And of course for Power BI, I need to automaticly add field with the "Field Nature" as the name of the data and the related data extract. Something like that :

Data Code   AgentCode EquipmentCode WorkOrderDate WorkOrderNumber PumpNumber NomimalVolume Year PumpedVolume

D001            Agent1       Equip1                1/1/2020            WO1

D001            Agent2       Equip2                2/1/2020           WO2

D002                                                                                                                    Pump1             500                     2018          4500    

D002                                                                                                                    Pump2             600                     2019          5000     

I can rename the field of every table/chart of my dashboards but I really want to avoid that...

I'va tested a lot of different solutions but no success.

Any Idea in PowerQuery/M ? (or DAX but I don't believe).

Thanks a lot.

[lhdp · Accepted solution]

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

[v-juanli-msft · Accepted solution]

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.

[lhdp]

Thanks to you ! 😀It's ok with of course some adjustment depend on my context. 

[Greg_Deckler]

So, what do you want? Sorry, I read this through three times and I do not have a clue what you want as output.

[lhdp · Accepted solution]

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