Build fact tables from RDBMS table

Hi Anonymous ,

Based on my understanding, you can transform your data like below first:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1TUw1TUyMAJxSioLUnELWiAJInFidaKVjEBCZmiajIzRdYFELYG0AhiDNIKVmGOxTQEFg5SaoCtVwFQWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date_reg_start = _t, process1_type = _t, date_finished_process1 = _t, process2_type = _t, date_finished_process2 = _t, date_reg_finished = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"id", "date_reg_start", "date_finished_process1", "date_finished_process2", "date_reg_finished"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"id", "date_reg_start", "date_reg_finished", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","_type","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","date_finished_","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Attribute] = [Attribute.1] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "process"}, {"Value", "type"}, {"Value.1", "date_finished"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"date_reg_start", type date}, {"date_reg_finished", type date}, {"date_finished", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"id", Int64.Type}, {"date_reg_start", type date}, {"date_reg_finished", type date}, {"process", type text}, {"type", type text}, {"date_finished", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"id", "date_reg_start", "process", "type", "date_finished", "date_reg_finished"})
in
    #"Reordered Columns"

 Then, you can create a Date dimension table and process, process type dimension table according to your requirements.

If this is not what you want, please share me more details.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.