Generate a Table Containing Metadata About Other Tables

[jdballard30]

I've brought several tables of a CRM system data into Power BI, and I'd like to use Power BI (if it makes sense) to try and generate some statistics about each column of each table. For example, say that I've brought in the account table with the following data:

AccountId	AccountName	Address1
abc123	Acme Corp	123 Main Street
xyz456	WidgetCo
mno225	Joe's Appliances	15503 US Highway 27

I'd like to generate a table that looks like this, based on the above table:

TableName	FieldName	MaxLength	BlankValues
Account	AccountId	6	0
Account	AccountName	16	0
Account	Address1	19	1

Is something like this possible? If so, I can't wrap my head around how to do it.

[v-alq-msft · Accepted solution]

Hi, @jdballard30 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Then you may create a blank query and input the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRecords(
       {
           [TableName="Account"]
       }
    ),
    #"Added Custom" = Table.AddColumn(Source, "AccountName", each Table.ColumnNames(Table)),
    #"Expanded AccountName" = Table.ExpandListColumn(#"Added Custom", "AccountName"),
    #"Added Custom1" = Table.AddColumn(#"Expanded AccountName", "MaxLength", each if [AccountName]="AccountId"
then 
List.Max(
    List.Transform(
    Table.Column(Table,"AccountId"),
    each Text.Length(_)
    )
)
else if [AccountName]="AccountName"
then
List.Max(
    List.Transform(
       Table.Column(Table,"AccountName"),
       each Text.Length(Text.From(_)) 
    )
)
else if [AccountName]="Address1"
then
List.Max(
    List.Transform(
       Table.Column(Table,"Address1"),
       each Text.Length(Text.From(_)) 
    )
)
else 
null
),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "BlankValues", each let 
tab1 = Table.SelectRows(Table,each [AccountId]=""),
tab2 = Table.SelectRows(Table,each [AccountName]=""),
tab3 = Table.SelectRows(Table,each [Address1]="")
in
if [AccountName]="AccountId"
then 
Table.RowCount(
    tab1
)
else if [AccountName]="AccountName"
then
Table.RowCount(
    tab2
)
else if [AccountName]="Address1"
then
Table.RowCount(
    tab3
)
else 
null)
in
    #"Added Custom2"

Result:

Best Regards

Allan

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

[Ajinkya369]

Hi @jdballard30 ,

Yes it is possible using the Power query editor.All you have to do is go to query editor -> Create a new step by clicking on fx

And then type this Function: Table.Schema(Source)

Table.Schema(Source)

For Has Blank value use a condtional column Go to Add column -> Condtional Column

if account id equal = leave blank then output 1

if account name equal = leave blank then output 1

if address equal = leave blank then output 1

else = 0

If your problem is solved then accept this reply as a solution

Thank you

Ajinkya

Master Data Analysis -