How do you document your models?

edit 01-Sep-2016: Simplified code

Hi David,

thanks for reporting the problem, but I cannot replicate it and couldn't find the reason.

And as the detour via Excel also is not very elegant and it is now very easy to copy the query-code, here comes a new approach. Just one query for PBI:

let

// ToDo: Copy the queries from the query-pane, click the gear-wheel icon near the query name "Source" in the Applied steps, remove the existing content and paste into the table. This will then be automatically translated into the JSON-string.
    ClickGearWheelClearAndPaste = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0tdXCCxNLao0VIrViVbKSS0B0wpAEJxfWpScqmCrAJHKzEOTAXPBBMwMI9xmINlBhEHG1DLIhIBBxtpIDsdiXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(ClickGearWheelClearAndPaste,{{"Column1", type text}}),

// Identifying query names
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},Text.Trim),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{},Text.Clean),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each ([Column1] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    LetIn = Table.AddColumn(#"Added Index", "LetIn", each if [Column1]="let" then 1 else if [Column1]="in" then -1 else 0),
    Level = Table.AddColumn(LetIn, "Level", (this)=>List.Sum(Table.SelectRows(LetIn, each [Index]<=this[Index])[LetIn])),
    QueryName = Table.AddColumn(Level, "QueryName", (this) => try if this[Index]=0 then this[Column1] else if Level[Column1]{this[Index]-2}= "in" and Level[Level]{this[Index]-2}=0 then this[Column1] else null otherwise null),
    #"Filled Down" = Table.FillDown(QueryName,{"QueryName"}),
    Replace = Table.Buffer(Table.ReplaceValue(#"Filled Down","// ","",Replacer.ReplaceText,{"QueryName"})),
    #"Added Custom" = Table.AddColumn(Replace, "Custom", each [Column1]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom","Custom",Splitter.SplitTextByEachDelimiter({"="}, QuoteStyle.Csv, false),{"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Custom.2] <> null)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Custom", each #table({"Column1"}, {{[Custom.2]}})),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.Select(Table.Transpose(Table.SplitColumn([Custom],"Column1",Splitter.SplitTextByAnyDelimiter({"+", "-", "/", "*", "(", ")", "}", "{", "[", "]", "#(lf)", "#(cr)", ","}, QuoteStyle.Csv)))[Column1], each _ <>"")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns1", "Custom.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom.1","#","",Replacer.ReplaceText,{"Custom.1"}),
    #"Trimmed Text1" = Table.TransformColumns(#"Replaced Value",{{"Custom.1", Text.Trim}}),
    Steps = Table.TransformColumns(#"Trimmed Text1",{{"Custom.1", Text.Clean}}),
    DistQueryNames = Table.Distinct(Replace, {"QueryName"}),
    TblQueryReferences = Table.NestedJoin(Steps,{"Custom.1"},DistQueryNames,{"QueryName"},"NewColumn",JoinKind.Inner),
    #"Added Custom3" = Table.AddColumn(TblQueryReferences, "Filter", each if [QueryName]=[Custom.1] then "Out" else "In"),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom3", each ([Filter] = "In")),
    QueryReferences = Table.RemoveColumns(#"Filtered Rows2",{"NewColumn"}),

// Prepare ParentChildTable
    StandaloneQueries1 = List.Difference(List.Distinct(Replace[QueryName]), List.Distinct(QueryReferences[QueryName])),
    StandaloneQueries = Table.FromList(StandaloneQueries1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenameStandalone = Table.RenameColumns(StandaloneQueries,{{"Column1", "Child"}}),
    Rename = Table.RenameColumns(QueryReferences,{{"Custom.1", "Parent"}, {"QueryName", "Child"}}),
    #"Removed Other Columns" = Table.SelectColumns(Rename,{"Child", "Parent"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    ChildParentTable = Table.Buffer(Table.Combine({#"Removed Duplicates", RenameStandalone}))
in
    ChildParentTable

It comes with some sample data included in step "ClickGearWheelClearAndPaste" already so it will return a table already that produces a sankey-diagram.

In order to fill it with the code of your queries, you just check your query names in the editor and copy. Then edit "ClickGearWheelClearAndPaste"-step by first deleting the existing content and then paste. Save&load and the new Sankey should appear. Have a look at the video: https://youtu.be/8QEuuO8mbKU

But be aware: Don't copy this code using one of Microsoft's browsers!!! It will break and return crazy error-messages!! (With almost all M-codes in this forum software unfortunately) I haven't had problems with Firefox.