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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Extracting data from lists and combining data

Hi everyone.
 
So I have a JSON file ( need to be ) with the specific format :
 
{
    "Id":[0,1,2],
    "Data1":[[5,1],[4,81,52,6,42,8,1,4,2,5],[18,36]],
    "Date2":[["u","j","a","e"],["q","s","d","f","p"],["n","b"]],
    "Date3":[[10,13],[10,25],[10,58]]
}
 
When I load my json file as a data source, I have :
base.PNG
 

The best result I can have ( by developping ID ) is this :

have.PNG
 
But I need to have data being like this :
Capture.PNG

 

So my question is : Can I have the result I want and if yes, how ?
2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@Anonymous  try this

let
    Source = Json.Document(File.Contents("C:\Users\user\Desktop\Daily\codebeautify.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Id", type any}, {"Data1", type any}, {"Date2", type any}, {"Date3", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({[Id],[Data1],[Date2],[Date3]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Column2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column3", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column4", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values2"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

AlexisOlson
Super User
Super User

Here is another possible solution:

 

let
    Source = Json.Document(File.Contents("C:\Users\aolson\Downloads\Sample.json")),
    #"Converted to Table" = Table.FromColumns(Record.FieldValues(Source), Record.FieldNames(Source)),
    #"Expand Lists" = Table.TransformColumns(#"Converted to Table", List.Transform(List.Skip(Table.ColumnNames(#"Converted to Table")), (col) => {col, each Text.Combine(List.Transform(_, Text.From), ","), type text}))
in
    #"Expand Lists"

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

Here is another possible solution:

 

let
    Source = Json.Document(File.Contents("C:\Users\aolson\Downloads\Sample.json")),
    #"Converted to Table" = Table.FromColumns(Record.FieldValues(Source), Record.FieldNames(Source)),
    #"Expand Lists" = Table.TransformColumns(#"Converted to Table", List.Transform(List.Skip(Table.ColumnNames(#"Converted to Table")), (col) => {col, each Text.Combine(List.Transform(_, Text.From), ","), type text}))
in
    #"Expand Lists"

 

Anonymous
Not applicable

Thank you, it works as I wish but I prefer the answer of smpa01 because I understand it better

smpa01
Super User
Super User

@Anonymous  try this

let
    Source = Json.Document(File.Contents("C:\Users\user\Desktop\Daily\codebeautify.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Id", type any}, {"Data1", type any}, {"Date2", type any}, {"Date3", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({[Id],[Data1],[Date2],[Date3]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Column2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column3", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column4", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values2"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Thank you, it works perfeclty and I understand well the different steps

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors