Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Matski469
Frequent Visitor

How to expand record without mostly null values

Within Power Query, using the desktop, I expand a column of records into multiple columns.  No issue there, except that the values I get are sort of stair-stepped with mostly null values around them like this:

Matski469_0-1702931627246.png

I found a solution for this before - use Group By within PQ, set aggregate to MIN (eventhough it's mostly text) and it seems to work fine.  However, it seems to bog the queries down a bunch because I have 50 or so columns to deal with, and I'm already at 20+ minutes to update less than 2,000 lines.

So I was wondering if there is a better way to do this? I'm trying to move things from PQ into the DAX area, so using DAX would be great, but I can't find a solution there either.  

1 ACCEPTED SOLUTION

Maybe try

 

Table.TransformColumns(Source, {"parameters", Record.Combine})

 

On the step when parameters is still a list.  This will provide cleaner expansion if each record does not have fields with null values.  I am hoping the nulls just come because you have a bunch of different records with different field names and when expanding the records at the same time you get nulls because the fields don't exist in other records.  When you click on an invidual cell you can see what the record looks like below.

 

spinfuzer_0-1703106327552.png

If records that are being combined have the same field, it will be overwritten by the latest value.

Combining [A = 1]  with [A = null, B = 2] will give you [A = null, B = 2].

Combining [A = 1] with [B = 2] will give you [A = 1, B = 2].

 

View solution in original post

13 REPLIES 13
Matski469
Frequent Visitor

That worked beautifully!  For others that are interested, I replaced:

#"Expanded parameters" = Table.ExpandListColumn(#"Prior step", "parameters"),

with:

#"Expanded parameters" = Table.TransformColumns(#"Prior step",{"parameters",Record.Combine}),

Very elegant solution to something that's been bothering me for quite some time.  Thank you!!!

Matski469
Frequent Visitor

I changed the code as you said, to: 

Matski469_0-1703104136764.png

It still comes in stepped.  Here are the steps it goes through as a visual sequence:

Matski469_1-1703104542785.png

 

Maybe try

 

Table.TransformColumns(Source, {"parameters", Record.Combine})

 

On the step when parameters is still a list.  This will provide cleaner expansion if each record does not have fields with null values.  I am hoping the nulls just come because you have a bunch of different records with different field names and when expanding the records at the same time you get nulls because the fields don't exist in other records.  When you click on an invidual cell you can see what the record looks like below.

 

spinfuzer_0-1703106327552.png

If records that are being combined have the same field, it will be overwritten by the latest value.

Combining [A = 1]  with [A = null, B = 2] will give you [A = null, B = 2].

Combining [A = 1] with [B = 2] will give you [A = 1, B = 2].

 

Matski469
Frequent Visitor

I tried the Table.Buffer idea, although that wasn't easy since the documentation is a bit sparse.  I think it saved a tiny bit.  I haven't tried the unpivot/pivot solution as I can't see a clean way to do that.  

 

When the data comes in, prior to expansion it looks like this:

Oper.Primary IDPre-Expansion parameters
1697-1-004[Record]
1697-1-004[Record]
B1697-1-004[Record]
B1697-1-004[Record]
C1697-1-004[Record]
C1697-1-004[Record]

 

After expansion, it looks like this:

Oper.Primary IDP1P2P3P4P5P6P7P8
1697-1-004Pilotnullnullnullnullnullnullnull
A1697-1-004null4nullnullnullnullnullnull
B1697-1-004Pilotnullnullnullnullnullnullnull
B1697-1-004nullnullnullnullnullnull0/5/2/0null
B1697-1-004nullnullnullnullnullnullnull6
C1697-1-004nullnull6nullnullnullnullnull
C1697-1-004nullnullnull20nullnullnullnull
C1697-1-004nullnullnullnull6nullnullnull
C1697-1-004nullnullnullnullnull20nullnull

 

Note that many are delimited, so my number of columns grows quickly too.  Note that after expansion, each parameter row only has one useful bit of information.  

 

My current solution is to bring all of this into one query (Process_Table).  I then create a new query using this:

Source = Table.SelectRows(Process_Table, each ([Operation] = "B"))

followed by removal of a bunch of columns.  I can then 'group by' and remove the nulls without much problem.  Again, not sure this is the best way, with "best" meaning quickest to process.

Why do we need the filtering for Operation B?  Can we just group by primary ID with operation all rows then change each _ to each Table.FirstN(Table.FillUp(_, Table.ColumnNames(_)),1).

 

What does each record look like?  Is there a way to clean it up and combine it so it does not expand so awkwardly into separate lines?  Is there some way to Group By ID and Record.Combine(_[parameter column) ?  Something like below:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRQ0lEyNLM01zXUNTAwAXKiFQIMFWwVYpQCMnPyS2KUFGKVYnVwKDQCKjSBKnAi0iBs6oyB6syA0rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Oper." = _t, #"Primary ID" = _t, #"Pre-Expansion parameters" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Oper.", type text}, {"Primary ID", type text}, {"Pre-Expansion parameters", type text}}),
    transform = Table.TransformColumns(#"Changed Type", {"Pre-Expansion parameters", each Expression.Evaluate(_)}), // just converting my example to records
    #"Grouped Rows" = Table.Group(transform, {"Primary ID"}, {{"parameters", each Record.Combine(_[#"Pre-Expansion parameters"])}})
in
    #"Grouped Rows"

 

 

I must apologize as I don't know how to implement your suggestion.  What we were given by the data host company is as follows (which may not be optimized):

 

BasicAuth parameter as "3t54ht343h5h"

lastNDays parameter as N

getData as follows:

(page as number) =>

let

authUrl = "https://app.xyz.com/api/authorization",
endpointUrl = "https://app.xyz.com/api/productions",
random1= Number.Random(),
startTime =

Number.ToText (Date.Year (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))) & "-" & Number.ToText (Date.Month (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))) & "-" & Number.ToText (Date.Day (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))),

Source = Json.Document(Web.Contents(authUrl,

[Headers= [ #"Content-Type" = "application/json",
Authorization = BasicAuth, #"Accept" = "*/*" ],
Content = Json.FromValue([scopes = {"productions_write"}, random=random1])])),
TokenTable= Record.ToTable(Source),
accessToken= #"TokenTable"{2}[Value],

Origin = Json.Document(Web.Contents(endpointUrl,

[Query= [access_token=accessToken, #"start-time-after" = startTime, page= Number.ToText(page) ],
Headers= [access_Token = accessToken, #"start-time-after" = startTime, page = Number.ToText(page) ]]))

in

Origin

And then the start of each query is this:

let

allProductionRecords = List.Generate(()=> [Result = if List.IsEmpty(getProdData(1)) then null else
getData(1), page=1],
each [Result]<>null,
each [Result = if List.IsEmpty(getData([page]+1)) then null else
getData([page]+1),page=[page]+1],
each [Result]),
#"Converted to Table" = Table.FromList(allProductionRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
etc.

Can we change this part of your list generate and check??

 

each [Result]<>null,
each [Result = if List.IsEmpty(getData([page]+1)) then null else
getData([page]+1),page=[page]+1]


into


each List.IsEmpty([Result]) = false,
each [Result = getData(page),page=[page]+1]

 

 

Next, if it is still then we need to see if we can transform the list of [Result] so that it does not expand into such a strange format.  It would help to see how some of the [Result] look like.

Matski469
Frequent Visitor

Here are the applied steps for one of my queries:

Matski469_0-1702997290995.png

In my file I have 13 queries, and most of them are at least similar to the above. In essense, pull in data from API, expand and choose what I want, then do necessary operations.  Unfortuneately, the 'parameters' steps is what causes the stair-stepped null value problem, and it's where I have most information.  What I'm doing is pulling in all process data that is collected in our manufacturing system (API) and then graphing, analyzing, etc.  The company that hosts our data is not very adept at PBI as well, so I'm sure there's some inefficiencies in there too.  

 

I've noticed that filtering takes forever, and so does grouping.  Most of the excess time is waiting for API, so it could be more of an API issue than PBI, that's why I'm trying to move as much as I can into the DAX area.  I'm a bit of a novice here so I've rebuilt my file from scratch a few times now to keep improving.  Over the holidays is rebuild #4 I believe.  

This stepped behavior on expansion usually occurs when a couple of columns should be combined prior to expansion and/or combined and then unpivoted.  That's why it will help to see some specific examples.

Try putting a Table.Buffer around your first step (or the first step that is a Table).  Maybe your query keeps repeating the API call and this can prevent that.  If it helps then keep the Table.Buffer.  If your first step is a list maybe try a List.Buffer.

Can you please provide some sample data to work with that has a similar structure to what you are working with?  You can keep you company data protected by changing it to something that can be shared.  For example, click on the Filtered Rows1 step, copy the preview data and paste it in Excel, then change the values so that they are content that can be shared.  Then paste the table into a message.  Do the same thing with the parameter values so we can see what the structure of those are.  Please include an explanation of how the parameter values relate to the previous step so we can sufficiently help.  Thanks!

jennratten
Super User
Super User

What is happening in the query before the columns are expanded.  I think that may be causing the issue.  If that is resolved then you can avoid having to fix the problem above.

spinfuzer
Super User
Super User

Maybe click on your unique columns, an unpivot on other columns and then pivot on Attribute and then Value with no aggregation.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.