Reply
Frequent Visitor
Posts: 7
Registered: ‎03-23-2016

DAX Studio & Power BI

Awesome tool, thanks for the recommendation.  Just installed it with ease.  Trying to output my measures to Excel but I don't have that option, only Grid, Timer and File.  Any idea how to activate output to Excel?

 

Also, is there any way to sort fields in data view of PBI?   I have dozens of measures and the table seems to sort them in the order they were created not in alpha order.  The measures are sorted in alpha order on the report view but not the data view which makes it very hard to find and edit measures when I have so many.  Any suggestions?

Super User
Posts: 1,191
Registered: ‎09-06-2015

Re: DAX Studio & Power BI

No, you have to export to file (txt) and then open it in Excel/Power Query.

 

And sorry, no idea about the sorting.

Frequent Visitor
Posts: 11
Registered: ‎11-18-2015

Re: How do you document your models?

Member
Posts: 321
Registered: ‎03-08-2016

Re: How do you document your models?

Interesting...I'll take a look.  I'll be especially interested in learning about the Azure Data Catalog.  It's surfaced in a few recent presentations by the Microsoft crowd.  Thanks, Tom

Member
Posts: 101
Registered: ‎12-14-2015

Re: How do you document your models?

@ImkeF just what i was looking for the Sankey chart looks like a perfect way to document both PQ queries and measures. Along with DAX studio to document tables and measure formulas this should save loads of time. Thanks a million.   :-)

Super User
Posts: 1,191
Registered: ‎09-06-2015

Re: How do you document your models?

Member
Posts: 101
Registered: ‎12-14-2015

Re: How do you document your models?

Hi @ImkeF i was trying to use your Power Query Management Studio solution yesterday but i encountered the following errrors. Can you please advise where i might be going wrong or if there is a work around.

 

I followed the instructions on that blog and when i click RefreshAll in your excel file i get an error ….”The connection could not be refreshed…..” and then another error stating “The following data range failed to refresh ExterneDaten_1” …..any suggestions ???

Highlighted
Super User
Posts: 1,191
Registered: ‎09-06-2015

Re: How do you document your models?

[ Edited ]

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.

Member
Posts: 101
Registered: ‎12-14-2015

Re: How do you document your models?

Wow.....Will try that @ImkeF ...but with 20 seperate queries rather than opening each in the advanced editor and copy & paste I assume I can do the "send a frown" and copy the M code section from that email ??? Hopefully.......Will try and let you know how i get on. Thanks for your continued support.   

Super User
Posts: 1,191
Registered: ‎09-06-2015

Re: How do you document your models?

[ Edited ]

Hi David,

no, unfortunately send frown will not work with this one. The syntax of the query names is different.

But it's much easier than you think: Check your first query -> press Shift -> check last query -> rightclick mouse -> copy:  done.

Much quicker than send frown.

Or If you have organized them in folders:: Just check the folders names and the copy will transfer all of them. No need to care about dependent queries - thay will come with it automatically.

This is btw also a very convenient method to copy queries across workbooks - also between PBI and Excel: Just instead of pasting them into a table, paste them into to editor. 

 

No need to open the adanced editor at all (apart from pasting the query above that does the job :-)