Data Modelling Assistance

[Anonymous]

Hi, 

I have the following Source table, 

GUID	Checked by A1-1	Pass or Fail A1-1	Checked by B1-1	Pass or Fail B1-1
0001	Name A	Pass	Name C	Fail
0002	Name B	Fail	Name A	Fail

, which I want to convert it in the following format - 

GUID	Asset	Checked by	Pass or Fail
0001	A1-1	Name A	Pass
0001	B1-1	Name C	Fail
0002	A1-1	Name B	Fail
0002	B1-1	Name A	Fail

I want to do this in Query Editor and should be dynamic in nature. Both GUIDS, Asset, and Fields may be added later on. In my actual dataset, there 500+ columns, and any query I do takes forever to refresh.

I am able to create a dynamic table or lists from the Column names like below, but not sure if that's helpful. Maybe cross join it somehow with the Source query? 

Checked by A1-1	Checked by	A1-1
Pass or Fail A1-1	Pass or Fail	A1-1
Checked by B1-1	Checked by	B1-1
Pass or Fail B1-1	Pass or Fail	B1-1

My ultimate goal is to normalize the fact table with 500+ columns into dimension tables somehow, and the request format may help. Still kind of exploring various methodologies. Any ideas on how this could be achieved in an efficient manner?

Appreciate the assistance!

[wdx223_Daniel · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUfJLzE1VcAQyAhKLi2F8ZyDDLTEzRylWB6zQCCbhBJNA0ghRGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GUID = _t, #"Checked by A1-1" = _t, #"Pass or Fail A1-1" = _t, #"Checked by B1-1" = _t, #"Pass or Fail B1-1" = _t]),
    Custom1 = Table.Combine(
                            Table.TransformRows(
                                                Source,
                                                each let
                                                        t=Table.SplitColumn(
                                                                            Table.Skip(Record.ToTable(_)),
                                                                            "Name",
                                                                            (x)=>let
                                                                                    a=Text.BeforeDelimiter(x," ",{0,1}),
                                                                                    b=Text.AfterDelimiter(x," ",{0,1})
                                                                                 in {[GUID],a,b},
                                                                            {"GUID","x","Assets"}
                                                                           )
                                                     in Table.Pivot(t,List.Distinct(t[x]),"x","Value")
                                               )
                           )
in
    Custom1